Why is there such a huge performance difference between these two queries?
-- (89 seconds)
SELECT max(mydate) FROM mytable WHERE eqpid = 'ABCDEFG'
AND mydate < sysdate - 5
vs.
-- (0.6 seconds)
SELECT max(mydate) FROM mytable WHERE eqpid = 'ABCDEFG'
AND mydate < TO_DATE('05/27/2011 03:13:00', 'MM/DD/YYYY HH24:MI:SS') -- 5 days ago
Regardless of indexes, it seems that both to_date and sysdate just return "some date value".
Notes: A composite index exists on this table including eqpid and 2 other columns. An index also exists for mydate. Both are b-tree. There are about 29 million rows.
Why would the optimizer choose such an obviously different (and in one case, awful) plan for these?
Jonathan Lewis has written about issues with sysdate
in 9i; have a look at the 'surprising sysdate' section here, for example. Essentially the arithmetic on sysdate
seems to confuse the optimizer, so in this case it thinks the index on mydate
is more selective. This seems like quite an extreme example though. (Originally pointed in this direction from a not-really-related Ask Tom post).
I don't know Oracle, but in Postgresql a possible source of ignoring an index is mismatched types. Maybe doing the straight - 5
makes Oracle think the rhs is numeric. Can you do a cast to date (or whatever is the exact type of mydate) on sysdate - 5
?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With