Large performance difference: Using sysdate vs using pre-formatted date

Question

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?

Thundercloud

What is the list of columns in the index for eqpid? Is is a combined index with eqpid down the list of columns? If so then Oracle might think that it isn't an efficient type index to use, and so it penalizes that plan. – Spindlelegs

@btilly: The composite primary key in this case includes 3 columns: eqpid (varchar2 8 byte), rectype (varchar1 1 byte), serialnobyte (number). It is my understanding that eqpid, the first in the key, can use the index. – Thundercloud

Yeah, it should be able to do so, and did in the second query. But you clearly have a lot of rows with that eqpid, and it must have shied away from it when it thought it had an alternative. The optimizer can do strange things. (But I miss it when I need to make MySQL run complex queries.) – Spindlelegs

Answer 1

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).

Giddy

I am surprised even Oracle let that get through QA. – Thundercloud

+1, I vaguely remembered reading about this but couldn't find the reference ;-) – Escort

Do you mean more selective, instead of less selective? – Spindlelegs

@Spindlelegs - er, yes. Corrected. Thanks! – Giddy

Answer 2

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?

Academy