Optimizer / Explain Plan

Please look at the following 3 queries which are very similar - but produce totally different execution plans (btw: using oracle 8.1.7).

1.)
select * from dwh$ta_c_table
where gueltig_von > to_date('26092002', 'DDMMYYYY')-30

Operation Object Name                         Rows  Bytes Cost 
SELECT STATEMENT Hint=CHOOSE                  748 K       464731 
TABLE ACCESS BY INDEX ROWID DWH$TA_C_table    748 K 177 M 464731 
INDEX RANGE SCAN DWH$IN_C_table_1_GUELTIGVON  748 K         1045 

Optimizer expects 748.000 Rows in result-set and decides to use an index-Scan, which is ok - for the table itself contains more than 130 Mio Rows.

2)
select * from dwh$ta_c_table
where gueltig_von > to_date('26092002', 'DDMMYYYY')-40

Operation Object Name                         Rows   Bytes   Cost
SELECT STATEMENT Hint=CHOOSE                   1 M         649421 
TABLE ACCESS FULL DWH$TA_C_table               1 M   340 M 649421 

Optimizer expects now about 1 Mio rows in result set, which again sounds reasonable.
So far nothing special - but now look at this:

3) Same statement as Nr 2, but now using sysdate instead of constant date
select * from dwh$ta_c_table
where gueltig_von > sysdate-40

Operation Object Name                         Rows   Bytes   Cost
SELECT STATEMENT Hint=CHOOSE                   7 M         649421 
TABLE ACCESS BY INDEX ROWID DWH$TA_C_table     7 M      1G 649421 
INDEX RANGE SCAN DWH$IN_C_table_1_GUELTIGVON   7 M           1782 

Ok, sysdate may be treated like a bind-variable and thus the Cost-Optimizer will produce incorrect results. But why this: Expecting 7 Mio Rows in result-set is nonsense! Costs are the same as in (2) - why? If the costs here are correct, than in Query 2, the index-fetch should be much cheeper, because only 1/7 of the rows are to be fetched (hope you know what I mean) - and a Full-Scan would make no sense!

Can someone explain this behaviour? Does someone know a "trick" how to use "sysdate" but still get correct optimizer-Results?

Thanks for your help/suggestions
Heiko Received on Mon Sep 30 2002 - 03:38:55 CDT