Re: Sub-query in order by clause

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" <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).