Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

performance current_date vs. to_date

510375Jan 4 2008 — edited Jan 9 2008
Hi,
I need to query records from a table for a 5 minute period 5 minutes in the past.
When I write my query this way it takes 115 seconds to complete:

select *
from
class.sdevvehicle_assign_unassign au
where
au.timestampdb >= current_date - (10/1440)
and au.timestampdb < current_date - (5/1440)

When I write the query this way it completes in 22 seconds:

select *
from
class.sdevvehicle_assign_unassign au
where
au.timestampdb >= to_date('20080104 10:20:00','yyyymmdd hh24:mi:ss')
and au.timestampdb < to_date('20080104 10:25:00','yyyymmdd hh24:mi:ss')

The 2 queries are functionally identical returning the same data but for the life of me I don't understand the performance difference. I really need to use the current_date approach as I want the query to dynamically pickup the 5 minute period 5 minutes in the past each time it is run.

Anybody have any ideas what may be causing the performance difference?

Thanks in advance for any help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 6 2008
Added on Jan 4 2008
9 comments
682 views