Home » SQL & PL/SQL » SQL & PL/SQL » Tuning order by clause (Oracle 9i/10g)
Tuning order by clause [message #432799] Thu, 26 November 2009 06:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #432805 is a reply to message #432802] Thu, 26 November 2009 06:35 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
In Fact, Oracle may even strip out the Outer query altogether and execute the two statements as the same execution plan.
Re: Tuning order by clause [message #432812 is a reply to message #432804] Thu, 26 November 2009 07:10 Go to previous messageGo to next message
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....
Re: Tuning order by clause [message #432813 is a reply to message #432812] Thu, 26 November 2009 07:16 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
possibly a temporal ripple in the t.s.c.?
Re: Tuning order by clause [message #432839 is a reply to message #432813] Thu, 26 November 2009 10:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
They ARE testing the LHC....
Re: Tuning order by clause [message #432842 is a reply to message #432839] Thu, 26 November 2009 10:27 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Very cool article in last week's new scientis on the LHC. Susy seems more interesting than Higgs to me Wink

[Updated on: Thu, 26 November 2009 10:35]

Report message to a moderator

Previous Topic: Oracle schedule job
Next Topic: how to update statment if difference between them is greater than 0.1
Goto Forum:
  


Current Time: Wed Feb 19 03:10:38 CST 2025