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
SCOTT@LSC69/7.3.4.5
SQL> select * from
(select level l from dual
connect by level < 10);
ERROR:
ORA-01436: CONNECT BY loop in user data
SCOTT@LSC65/8.1.7.4
SQL> select * from
(select level l from dual
connect by level < 10);
ERROR:
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
tahiti.oracle.com to start reading
