Home » RDBMS Server » Performance Tuning » performance problem with DATE field Comparison (Oracle 9i)
performance problem with DATE field Comparison [message #309275] Wed, 26 March 2008 23:50 Go to next message
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 #309315 is a reply to message #309275] Thu, 27 March 2008 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the requested information for tuning query question and first post your query!

Also read How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.

Regards
Michel
Re: performance problem with DATE field Comparison [message #309332 is a reply to message #309275] Thu, 27 March 2008 02:22 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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
Re: performance problem with DATE field Comparison [message #310061 is a reply to message #309275] Mon, 31 March 2008 02:23 Go to previous message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Thank you...
Previous Topic: Creating Materialized View using Union All Operator
Next Topic: reg performance tuning concept
Goto Forum:
  


Current Time: Sun Dec 04 18:46:25 CST 2016

Total time taken to generate the page: 0.04191 seconds