| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Puzzling execution plan
(Oracle 9.2.0.4) This doesn't make sense to me...
Table: Call it MYTAB - with about 50 million rows and 12 GB or so.
Column: Call it INSERT_DATE (not quite, but close)
Primary key: via single column unique index on sequence-generated column -
call id PK_ID
Index: on INSERT_DATE - call it MYTAB_IDX01
Statistics: Fresh - and attempted several ways - via DBMS_STATS, analyze
table MYTAB estimate statistics for table for all indexes, <the latter> +
for all indexed columns, with sample size up to 40 percent, etc.
Query 1: select PK_ID from MYTAB where INSERT_DATE > trunc(sysdate)
Query 2: select PK_ID from MYTAB where INSERT_DATE >
to_date('11-JUN-2004','DD-MON-YYYY')
Query 3: select PK_ID from MYTAB where INSERT_DATE > sysdate -1
Query 1 : *Always* does a full table scan - and takes forever. (What was
actually wanted.)
Query 2 : *Always* does a full table scan - and takes forever. (Same
logic/results as Q1, but more "cumbersome".)
Query 3 : *Always* does an index range scan on MYTAB_IDX01 - and takes a
minute or less. (Not the same logic at all, but it does finish!)
I tried all these later in the day also (~11 PM) when the results would be fairly close (about 0.07% of the records) - with the same results.
How does this make sense? What might make an optimizer with fresh statistics so badly munge the execution plan for Q1 & Q2? This was in a database with no tweaks to OIC/etc. A hint "fixes" Q1 and Q2, but it seems one should not be necessary.
-Don Granaman
puzzled OraSaurus
 |
 |