| performance problem with DATE field Comparison [message #309275] |
Wed, 26 March 2008 23:50  |
me_arindam
Messages: 26
Registered: March 2008
Location: India
|
Junior Member |
|
|
Hi,
I have a column called extract_date (date field) in my table.
whenever I am using a condition(in the where clause)like this,
extract_date <= sysdate-2
it is taking huge time to execute the query.
But if I put only extract_date<=sysdate
it execute faster...
No only this,
whenever arithmetic function is used with date field like
(extract_date-sysdate)*24*60*60 <= 430000000
it is taking huge time.
Again sometimes these kind of operations working faster with > operator.
Please advise me why this kind of situation arise. Is any specific solutions are there.
Thanks,
Arindam
|
|
|
|
|
|
| Re: performance problem with DATE field Comparison [message #309332 is a reply to message #309275] |
Thu, 27 March 2008 02:22  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle can only perform an index scan on an indexed column when it is used unaltered (no functions, arithmetic, concatenation)
col < sysdate - 2 should be OK
col + 2 < sysdate will not scan the index
Oracle's Cost-based Optimiser is getting confused when you use
col < sysdate - 2
and
col < sysdate
The first version CAN use an index but perhaps chooses not to.
You would have to post Explain Plans for both versions.
Ross Leishman
|
|
|
|
| Re: performance problem with DATE field Comparison [message #309333 is a reply to message #309275] |
Thu, 27 March 2008 02:25 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. Do you have an index with extract_date column in first position?
-- If NOT then Oracle will perform FULL table scan of your table.
2. The usage of any function (as in your example)
(extract_date-sysdate)*24*60*60 <= 430000000
prevents index access and full table scan is used.
rewrite it as:
extract_date<= (sysdate + 4300000/864)
HTH.
Michael
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
