Home » RDBMS Server » Performance Tuning » Query tuning
Query tuning [message #423233] Tue, 22 September 2009 23:25 Go to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Hi,

I'm executing the below query, I can fetch the records in 55 sec.

Quote:
select * from charge_vw where TRUNC (last_update_date)
BETWEEN TO_DATE ('10/01/2007', 'MM/DD/YYYY')
AND TO_DATE ('10/20/2007', 'MM/DD/YYYY')


But if I run the same query by passing the values from a table it is running for more than 20 mins
and not fetching any records(below Query).
(In the existing query I just modified the between clause statements).


Quote:
select * from charge_vw where TRUNC (last_update_date)
BETWEEN (select trunc(max(start_date)) from wk_date)
AND (select trunc(max(end_date)) from wk_date)


What is the problem with the modified query? How can i tune this query to make it faster?

Thanks..

Report message to a moderator

Re: Query tuning [message #423236 is a reply to message #423233] Wed, 23 September 2009 00:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I just modified the between clause statements).

Quote:
What is the problem with the modified query?

Just? You ask Oracle to scan twice more your table.

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

Regards
Michel

Report message to a moderator

Re: Query tuning [message #423301 is a reply to message #423233] Wed, 23 September 2009 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The difference is that for the first query, the optimizer knows what the values that you're searching between are, and can base it's execution plan on that information.
In the second query, the optimiser has no idea what the upper and lower range values are, and will use it's default values.

If you post the Explain Plans for the two queries, you will see substantial differences between them.

Report message to a moderator

Re: Query tuning [message #423463 is a reply to message #423301] Thu, 24 September 2009 08:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've noticed this behavior before in v9i. Scalar sub-queries in range comparisons seem to prevent both index scans and partition pruning.

It could still be a problem in current versions.

Run an explain plan of both queries. If I'm right, you won't be able to force an index scan even with hints.

The only solution I have found is to put the scalar subquery inside a PL/SQL function.
Ross Leishman

Report message to a moderator

Re: Query tuning [message #423510 is a reply to message #423233] Thu, 24 September 2009 21:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Does the following not do the same thing as your original query?

select *
from charge_vw
where last_update_date
     BETWEEN TO_DATE ('10/01/2007', 'MM/DD/YYYY')
         AND TO_DATE ('10/20/2007', 'MM/DD/YYYY')+1-(1/24/60/60)
/

May I suggest you try this instead:

select *
from charge_vw
where last_update_date
     BETWEEN trunc((select max(start_date) from wk_date))
         AND trunc((select max(end_date) from wk_date))+1-(1/24/60/60)
/

Along with these indexes:

wk_date(start_date)
wk_date(end_date)
charge_vw(last_update_date)

With luck, you will see and index plan that uses all three indexes with MAX/MIN of <index> done on the two singleton selects. Indeed, maybe your 55 seconds will become .55 seconds. You might also wish to try the cardinality hint the singleton selects.

Or maybe Ross is right and you just have to wait for a fix.

Good luck, Kevin

Report message to a moderator

Re: Query tuning [message #423514 is a reply to message #423463] Thu, 24 September 2009 22:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 24 September 2009 23:22
I've noticed this behavior before in v9i. Scalar sub-queries in range comparisons seem to prevent both index scans and partition pruning.

It could still be a problem in current versions.

Run an explain plan of both queries. If I'm right, you won't be able to force an index scan even with hints.

The only solution I have found is to put the scalar subquery inside a PL/SQL function.
Ross Leishman

Just ran a test on 10.2 and this did NOT happen. It appears that if ever it was a problem anywhere but in my own memory, it is now gone. Thinking back, I now reckon it was v8i, not 9i.
Ross Leishman

Report message to a moderator

Re: Query tuning [message #423855 is a reply to message #423233] Mon, 28 September 2009 12:21 Go to previous message
madhuottapalam
Messages: 10
Registered: September 2009
Junior Member
gajini wrote on Tue, 22 September 2009 23:25
Hi,

I'm executing the below query, I can fetch the records in 55 sec.

Quote:
select * from charge_vw where TRUNC (last_update_date)
BETWEEN TO_DATE ('10/01/2007', 'MM/DD/YYYY')
AND TO_DATE ('10/20/2007', 'MM/DD/YYYY')


But if I run the same query by passing the values from a table it is running for more than 20 mins
and not fetching any records(below Query).
(In the existing query I just modified the between clause statements).


Quote:
select * from charge_vw where TRUNC (last_update_date)
BETWEEN (select trunc(max(start_date)) from wk_date)
AND (select trunc(max(end_date)) from wk_date)


What is the problem with the modified query? How can i tune this query to make it faster?

Thanks..


Can you declare two variable to store start_date from WK_Date and end_date from Wk_date and select start-date and end_date from WK_Date first and then pass that variable in the WHERE CONDITIOn. See if it makes any difference.

Madhu

Report message to a moderator

Previous Topic: Performance tuning in Oracle
Next Topic: Slow Insert APPEND into Temporary Table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 06:30:16 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.