Sub-query in order by clause
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
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
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
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
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
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
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
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)