reduce the elapsed time [message #461882] |
Tue, 22 June 2010 01:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
akilabaskaran
Messages: 29 Registered: May 2007 Location: chennai
|
Junior Member |
|
|
Hi,
i have a sample code as below.
Kindly let me know what can be done to reduce the elapsed time of the below query
select col1 from a where a.end_date is null and a.start_date <(sysdate-(xhours/24))
order by a.start_date
|
|
|
|
|
Re: reduce the elapsed time [message #461901 is a reply to message #461894] |
Tue, 22 June 2010 01:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
akilabaskaran
Messages: 29 Registered: May 2007 Location: chennai
|
Junior Member |
|
|
The explain plan is as below
SELECT STATEMENT ALL_ROWSCost: 7 K Bytes: 3 K Cardinality: 132
2 SORT ORDER BY Cost: 7 K Bytes: 3 K Cardinality: 132
1 TABLE ACCESS FULL TABLE a Cost: 7 K Bytes: 3 K Cardinality: 132
|
|
|
|
|
Re: reduce the elapsed time [message #461961 is a reply to message #461955] |
Tue, 22 June 2010 04:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Did you run the explain plan against a dev/test db that has a lot less rows in it?
The explain plan thinks that table is practically empty.
Regardless, if you don't have any indexes a full table scan is the only option, try adding an index on start_date.
|
|
|
|
Re: reduce the elapsed time [message #462034 is a reply to message #461882] |
Tue, 22 June 2010 08:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
IMHO
1. If xhours is a table column then the expression
a.start_date < (sysdate-(xhours/24)) is not indexable and Oracle must use full table scan.
2. Let's assume that xhours is parameter. In that case you are selecting all rows older then (sysdate-(xhours/24)).
So (unless your table holds future dates) you are selecting almost all data - full table scan is used to get a better performance.
3. How many rows your query is supposed to select?
HTH.
[Updated on: Tue, 22 June 2010 08:14] Report message to a moderator
|
|
|
|