Re: Optimizer / Explain Plan

This could be affected by the precision of the data and the query.

Is the data stored with a time, or is it stored with just a date component ? For experimental purposes, change the '>' to '>=', and change the sysdate to trunc(sysdate). You may get some clues which help you to understand what is going on.

As ever, check that the statistics on the table and index are reasonably accurate and up to date. And in particular that the values for 'number of distinct values', low value and high value are suitable on this date column.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9 (MI), 19/21 (TX)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Heiko Welter wrote in message
<5b9217b5.0209300038.32f28ca1_at_posting.google.com>...


>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