Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sub-query in order by clause

Re: Sub-query in order by clause

From: Jonathan Lewis <>
Date: Fri, 04 Apr 2003 10:53:39 -0800
Message-ID: <>

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).


Jonathan Lewis

  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:

____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:

____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> Hello list,
> In Oracle, 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

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Fri Apr 04 2003 - 12:53:39 CST

Original text of this message