Sub-query in order by clause

2003-04-04 Thread Ashish



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, enameFROM empORDER 
BY (SELECT deptnoFROM 
deptWHERE dept.deptno = emp.deptno 
);

Thequestion I 
have is what is theusefulness of this?Under which 
circumstances
this can be used? 
Any ideas?

-Ashish
OCP 
DBA



Re: Sub-query in order by clause

2003-04-04 Thread Jonathan Lewis

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



RE: Sub-query in order by clause

2003-04-04 Thread Nicoll, Iain
Don't really know but couldn't it be useful if you had 
 
ORDER BY ( SELECT deptname FROM dept
WHERE dept.deptno = emp.deptno)
 
 
-Original Message-
Sent: 04 April 2003 17:34
To: Multiple recipients of list ORACLE-L


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: Nicoll, Iain
  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).



RE: Sub-query in order by clause

2003-04-04 Thread Jacques Kilchoer
Title: RE: Sub-query in order by clause





Why would you do that instead of
SELECT emp.deptno, empno, ename
FROM emp a, dept b
WHERE dept.deptno = emp.deptno
order by dept.deptname ;


 -Original Message-
 From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
 
 Don't really know but couldn't it be useful if you had 
 
 ORDER BY ( SELECT deptname FROM dept
 WHERE dept.deptno = emp.deptno)
 
 
 -Original Message-
 From: Ashish [mailto:[EMAIL PROTECTED]]
 
 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?





RE: Sub-query in order by clause

2003-04-04 Thread Nicoll, Iain
Jacques,
 
As I believe has been pointed out already it may possibly be more efficient
if dept is very small and emp is very large (especially if there are filters
and a join would be done before a filter was applied).  Alternatively it may
be that Oracle believe it is more intuitive to people with no preconceived
ideas.
 
Iain Nicoll
-Original Message-
Sent: 04 April 2003 20:24
To: Multiple recipients of list ORACLE-L



Why would you do that instead of 
SELECT emp.deptno, empno, ename 
FROM emp a, dept b 
WHERE dept.deptno = emp.deptno 
order by dept.deptname ; 

 -Original Message- 
 From: Nicoll, Iain [ mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] ] 
 
 Don't really know but couldn't it be useful if you had 
  
 ORDER BY ( SELECT deptname FROM dept 
 WHERE dept.deptno = emp.deptno) 
  
  
 -Original Message- 
 From: Ashish [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] 
 
 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? 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nicoll, Iain
  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).



RE: Sub-query in order by clause

2003-04-04 Thread Lyndon Tiu
Which way is faster? The subselect(nested select) or the join?

Thanks.

-- 
Lyndon Tiu


Quoting Jacques Kilchoer [EMAIL PROTECTED]:

 Why would you do that instead of
 SELECT emp.deptno, empno, ename
 FROM emp a, dept b
 WHERE dept.deptno = emp.deptno
 order by dept.deptname ;
 
  -Original Message-
  From: Nicoll, Iain [mailto:[EMAIL PROTECTED]
  
  Don't really know but couldn't it be useful if you had 
   
  ORDER BY ( SELECT deptname FROM dept
  WHERE dept.deptno = emp.deptno)
   
   
  -Original Message-
  From: Ashish [mailto:[EMAIL PROTECTED]
  
  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?
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Lyndon Tiu
  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).



RE: Sub-query in order by clause

2003-04-04 Thread Jacques Kilchoer
Title: RE: Sub-query in order by clause





I see your point. The answer to my question why would you do that instead of ...  is, of course, because the other way might be faster.

Read Jonathan Lewis' earlier post, he had (as always) some good insight.


 -Original Message-
 From: Lyndon Tiu [mailto:[EMAIL PROTECTED]]
 
 Which way is faster? The subselect(nested select) or the join?
 
 Quoting Jacques Kilchoer [EMAIL PROTECTED]:
 
  Why would you do that instead of
  SELECT emp.deptno, empno, ename
  FROM emp a, dept b
  WHERE dept.deptno = emp.deptno
  order by dept.deptname ;
  
   -Original Message-
   From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
   
   Don't really know but couldn't it be useful if you had 
   
   ORDER BY ( SELECT deptname FROM dept
   WHERE dept.deptno = emp.deptno)





RE: Sub-query in order by clause

2003-04-04 Thread Jacques Kilchoer
Title: RE: Sub-query in order by clause





Yes, I see that. (hangs head in shame)


 -Original Message-
 From: Nicoll, Iain [mailto:[EMAIL PROTECTED]]
 
 As I believe has been pointed out already it may possibly be 
 more efficient
 if dept is very small and emp is very large (especially if 
 there are filters
 and a join would be done before a filter was applied). 
 Alternatively it may
 be that Oracle believe it is more intuitive to people with no 
 preconceived
 ideas.
 
 -Original Message-
 Sent: 04 April 2003 20:24
 
 Why would you do that instead of 
 SELECT emp.deptno, empno, ename 
 FROM emp a, dept b 
 WHERE dept.deptno = emp.deptno 
 order by dept.deptname ; 
 
  -Original Message- 
  From: Nicoll, Iain [ mailto:[EMAIL PROTECTED]
 mailto:[EMAIL PROTECTED] ] 
  
  Don't really know but couldn't it be useful if you had 
  
  ORDER BY ( SELECT deptname FROM dept 
  WHERE dept.deptno = emp.deptno)