You are now on my old blog. Please update your bookmarks to my new blog

08 November 2005

Bible of Oracle

It is the second time I got an answer like : it is a bug in the documentation.

First was about single table sort hashed cluster, when the doc does not say that you need to use order by to sort the rows.
Ref: tom kyte blog : order in the court This is just a warning that unless your query has an ORDER BY, you have no reason to anticipate the data being returned to you in any kind of sorted order whatsoever
Ref: the documentation 10gR2 Admin Guide : Given the sort order of the data, the following query would return the call records for a specified hash key by oldest record first.
SELECT * WHERE telephone_number = 6505551212;

Tom assumes it is a bug in the doc. But still it is sorting the rows as described in the doc.

The second example is about a Mikito Harakiri hack. In my opinion, it is not legal to use connect by without prior. And it has never been since Oracle 7. I do not have Oracle 6 or Oracle 5 doc, so if you have it, drop me a mail if it was different.
Ref: asktom Can there be an infinite DUAL?
select level from dual connect by level < :N

Ref: the doc 10gR2 SQL Ref In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row
Ref: the doc 7.3.4 SQL Ref connect by clause : some part of the condition must use the PRIOR operator to refer to the parent row

Well, I am deeply disappointed about those answers. In the first example, a sorted hash cluster is a special case. Why is the doc wrong? If you select from a sorted single table hash cluster, you get your result sorted. There could be limit case (like nls_sort), where the sorting algorythm could be different than the one you would get by using "order by", and specifying an "order by" will not necessarly dicrease the performance. But the data IS sorted as stated in the doc.

In the second case, Tom is using an illegal construct. In Oracle 7, 8 and 8i, it was generating an ORA-1436

SQL> select * from
(select level l from dual
connect by level < 10);
ORA-01436: CONNECT BY loop in user data

SQL> select * from
(select level l from dual
connect by level < 10);
ORA-01436: CONNECT BY loop in user data

I would tend to argue that the fact that it works in 9i is bug.

Most of the questions could be answered in the doc. Sometimes, quite often actually, I send feedback to the OTN documentation forum. But only when I can prove there is something wrong. And I have never see a correction in the doc. I urged OTN team to take care of my feedback. But never I would say : the doc is wrong. Unless I can prove it.

Nevertheless, the documentation is not open-source. It is not a collection of customer statements. It is a reference and everything what is stated in the doc is supported, and something that would not be stated in the doc will probably not be supported by Metalink.

I once suggested in asktom
select stragg(distinct ename) from emp;

to sort the ename's. It DOES sort the rows. But it is not WRITTEN in the doc that udag(distinct n) does sort the rows, so I cannot pretend it will. You probably would not be able to find an counter-example, but, since it is not stated in the doc, I gave me beaten on that one.

I hope I have correctly explained my point of view :
The ultimate oracle book is oracle documentation. Go to to start reading


Blogger Gary Myers said...

I had a documentation error fixed for the DUMP function and nulls. 9i documentation said it would return null. The fix in 10Gr2 docs say it returns the string 'NULL'. Incidentally, the Oracle7 documentation was correct.

The 'chiken and the egg' question is whether the documentation should reflect how Oracle works, or whether Oracle should work as specified by the documentation.

Related to stragg and distinct, I use a variant that uses an associative array (index by varchar2), but again there's no definitive statement that says the ordering mechanism when looping through the array with .FIRST/.NEXT. I THINK its more likely to guarantee a correct answer in future than distinct....

8/11/05 22:20  
Anonymous Anonymous said...

ORDER BY is required to guarantee ordered resulty set. Though many Oracle functions and features internally sort data to allow a result set to be provided, it is only a "feature" and not guaranteed in any document.

At least this is what I learned from the gospel according to Chris Date!

22/5/06 16:26  
Blogger Laurent Schneider said...

well, the documented named above seems to say a sorted hash cluster will return the data in a sorted maneer, but well, it may be a bug in the docu...

22/5/06 20:30  

Post a Comment

<< Home