Home » RDBMS Server » Performance Tuning » Improving performance of a query using order by clause
Improving performance of a query using order by clause [message #240901] Sun, 27 May 2007 08:25 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I wanna to get the top ten tasks from a table based on the execution time of a task

the table
ad_tasks contains columns task_id , exec_time ,tg_id etc

the table ard 7 lakh rows

the query is like this

select task_id from ( select * from ad_tasks where tg_id=1 order by exec_time desc nulls last ) where rownum < 11;

can i tune this query to make its performance better ?
Re: Improving performance of a query using order by clause [message #240908 is a reply to message #240901] Sun, 27 May 2007 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't gave so much different tg_id and don't make many DML, use a bitmap index on it.

Regards
Michel
Re: Improving performance of a query using order by clause [message #240911 is a reply to message #240901] Sun, 27 May 2007 09:46 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have already tried creating a bitmap index on it but still the query's performance hasn't improved that much ...

any other way of improving the performance ?
Re: Improving performance of a query using order by clause [message #240912 is a reply to message #240911] Sun, 27 May 2007 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Improving performance of a query using order by clause [message #241005 is a reply to message #240912] Mon, 28 May 2007 03:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Disagree: Yes, well... maybe.

If you index (tg_id,exec_time), then Oracle can use it to honour the ORDER BY.

select task_id 
from ( 
    select * 
    from ad_tasks 
    where tg_id=1 
    order by exec_time desc
) where rownum < 11


Try with and without the NULLS LAST - I'm not sure you can use it with this technique. I'm not sure whether this will work with the DESC order either, but you can try.

Ross Leishman
Re: Improving performance of a query using order by clause [message #241183 is a reply to message #241005] Mon, 28 May 2007 11:54 Go to previous message
dbaxchangedba
Messages: 26
Registered: November 2005
Junior Member
Quote:
I'm not sure whether this will work with the DESC order either


DESC on exec_time might help...

(tg_id,exec_time desc)

Also,get rid of * from the select
select * from ad_tasks where tg_id=1 order by exec_time desc 


Instead use the task_id column in your select. This will prevent an extra IO in the form of "table access (by index rowid)"

Good Luck.....

http://www.dbaxchange.com
Previous Topic: Physical gets increasing after scheduled export
Next Topic: Need help
Goto Forum:
  


Current Time: Wed Dec 07 12:18:25 CST 2016

Total time taken to generate the page: 0.09620 seconds