Search

Top 60 Oracle Blogs

Recent comments

SQL

Watching Consistent Gets – 10200 Trace File Parser

January 24, 2011 It happened again, another blog article that forced me to stop, think, and … hey, why did Oracle Database 11.2.0.2 do something different than Oracle Database 10.2.0.5?  What is different, even when the OPTIMIZER_FEATURES_ENABLE parameter is set to 10.2.0.4 (or 10.2.0.5)?  The number of consistent gets for a SQL statement is significantly different - we did [...]

Query is Returning ORA-06502: Character String Buffer Too Small, Any Help for the OP?

January 21, 2011 I found an interesting SQL statement on the OTN forums today.  When executing the SQL statement Oracle Database returns the following error: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 12 ORA-00920: invalid relational operator 00604. 00000 - "error [...]

Adding Comments to SQL Statements Improves Performance?

January 15, 2011 While reading the “Pro Oracle SQL” book I learned something interesting.  Commenting your work can improve database performance.  You certainly are aware that thoroughly documenting what you do could prevent hours of headaches that might appear later when trying to investigate problems or add additional features to an existing procedure (I think that was [...]

Free ANSI SQL to Oracle Specific SQL Translator and SQL Tutor

January 7, 2011 In a recent OTN thread a developer described their difficulty in working with ANSI SQL in Oracle Database 8i (quick show of hands, how many people know why?).  The OP eventually asked: “I don’t suppose there are any normal-query-sytax-to-freakishly-old-syntax converters out there?” And John Spencer jokingly replied back: “Personally, I’m looking for a freakishly-new-syntax-to-normal-query-sytax [...]

NO_QUERY_TRANSFORMATION Hint is Ignored… Well, Almost Ignored

January 3, 2011 A couple of days ago I used a NO_QUERY_TRANSFORMATION hint to permit a query (provided by Martin Berger) to execute, which on the surface appeared to be quite sane, but without hints the query would usually fail to execute.  A test case version of the query worked on 11.2.0.1 running on 64 bit Windows on [...]

Analytic Functions – What is Wrong with this Statement?

January 1, 2011 I was a bit excited to see the chapter discussing analytic functions in the book “Pro Oracle SQL”, which has a rather extensive coverage of most of Oracle Database’s analytic functions (something that I have not seen from other SQL books).  That chapter is very well assembled, with easier to understand descriptions [...]

ANSI Full Outer Join, Ready or Not?

December 30, 2010 (Modified January 1, 2011) When I read pages 101-103 of the book “Pro Oracle SQL” a couple of days ago, I was reminded of a couple of things.  This section of the book describes full outer joins, showing the ANSI syntax and Oracle syntax to perform a full outer join.  If you read [...]

Hash Joins – What is Wrong with this Statement?

December 29, 2010 I started reading the book “Pro Oracle SQL” a couple of days ago, and I am having trouble putting it down.  Other than a couple of minor word substitutions and intended, but not specified, qualifying words, the first 100 pages of the book are fantastic (I suspect that many of these cases [...]

Feeling ANSI About Oracle Join Syntax?

December 26, 2010 Yesterday I started reading another book on the topic of Oracle SQL (for the moment I will keep the title of the book a mystery).  I am not much of a fan of ANSI style syntax – that syntax style is easy to read when there are just two tables involved, but [...]

Explain Plan Shows a Cartesian Merge Join, How Would You Help?

December 24, 2010 Imagine that a developer approached you with the following SQL statement, and explained that he (or she) noticed that the execution plan showed a Cartesian merge join.  Even adding an ORDERED hint did not affect the execution plan, and the execution performance was the same with the ORDERED hint. SELECT X.TIME_PERIOD EXPOSURE_PERIOD,Y.TIME_PERIOD [...]