Wow ! I didn't realise that was possible - I'll have to see what Oracle can do with the execution plan.
Your example could, of course, be rearranged to join emp to dept in order to order by some column of dept - and that might give us a clue why this could be useful. Pure hand-waving here - not arguing for or against - but what if you have a heavy-duty multitable query that returned only a few rows. It could be the case that joining in 'dept' (i.e. just one more table needed to supply the ordering) would require that join to be exercised a very large number of times, either as a nested loop, or as an extra hash join. Whereas if you effectively joined in 'dept' after the query was complete as per your example, the additional cost would be small. (In some ways, this is like taking advantage of the 'push_subq' hint to rearrange the moment at which Oracle executes a non-mergeable subquery.) In many cases though (if not all) I suspect the same effect could be produced by using a no_merge inline view - which has the added benefit of being intuitively a little more readable. (Although, your site may have a "no hints" standard that could preclude the use of the inline view option). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____UK_______April 8th ____UK_______April 22nd ____Denmark__May 21-23rd ____USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK_(Manchester)_May ____Estonia___June (provisional) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 04 April 2003 17:33 > Hello list, > > In Oracle 9.2.0.1, you can now order by using a single-row subquery > Here is an example: > > SELECT emp.deptno, empno, ename > FROM emp > ORDER BY ( SELECT deptno FROM dept > WHERE dept.deptno = emp.deptno ); > > The question I have is what is the usefulness of this? Under which > circumstances > this can be used? Any ideas? > > -Ashish > OCP DBA > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
