Tuning order by clause [message #432799] |
Thu, 26 November 2009 06:06  |
merawalaid
Messages: 2 Registered: May 2007
|
Junior Member |
|
|
I am trying to reduce the run time of some of the frequently used queries in my application.
I found that 'order by' clause becomes very expensive for a large table when the colums in the order by are not part of an index.
My question is:
Does a normal 'order by' clause sorts the entire table, irrrespective of WHERE clause?
eg:
Query 1:
select * from employee_tbl where deptid=57 and sex='M' order by empname,empno;
Query 2:
select * from (select * from employee_tbl where deptid=57 and sex='M' order by empname,empno) order by empname,empno;
Will query 2 run faster every time?
|
|
|
Re: Tuning order by clause [message #432801 is a reply to message #432799] |
Thu, 26 November 2009 06:19   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
Oracle should perform this is stages. Please refer to SQL tuning forum.
You should also read the posting rules, especially about the "CODE" formatting
Hint: Use "explain plan" and possibly check statistics of the query.
And the answer to your question: No, it should not work faster due to what you have specified but you should read the SQL tuning forum anyway.
[Updated on: Thu, 26 November 2009 06:21] Report message to a moderator
|
|
|
Re: Tuning order by clause [message #432802 is a reply to message #432799] |
Thu, 26 November 2009 06:21   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Query 2 will, in general, run slower, as you are performing two sort operations, and not one.
I strongly suspect that the reason that you see it running faster is becasue you are running it AFTER you've run the first query, and so that data that you need is already cached and does not need to be re-read from the disk.
|
|
|
Re: Tuning order by clause [message #432804 is a reply to message #432799] |
Thu, 26 November 2009 06:32   |
surachart
Messages: 19 Registered: December 2008
|
Junior Member |
|
|
Query 1:
select * from employee_tbl where deptid=57 and sex='M' order by empname,empno;
Query 2:
select * from (select * from employee_tbl where deptid=57 and sex='M' order by empname,empno) order by empname,empno;
You should check from execution plain... or dbms_xplan
http://www.psoug.org/reference/dbms_xplan.html
If you query1 and then query 2... Query 2 will faster Query 1 anyway.
Because Query 1 read data from disk to memory... Query 2 just read from memory (if you query 2 after query 1)
|
|
|
|
Re: Tuning order by clause [message #432812 is a reply to message #432804] |
Thu, 26 November 2009 07:10   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:If you query1 and then query 2... Query 2 will faster Query 1 anyway.
Because Query 1 read data from disk to memory... Query 2 just read from memory (if you query 2 after query 1)
I could have sworn I said the same thing 10 minutes earlier.
I guess there's an echo....
|
|
|
|
|
|