performance current_date vs. to_date
510375Jan 4 2008 — edited Jan 9 2008Hi,
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.