Index Hint Being Ignored
412426Mar 9 2006 — edited Mar 10 2006I'm sure that I'm missing something here, but I'm not seeing it. I have the following query that I have explained with and without hints -- using 'index' and 'index_ffs', and the explain always shows me the same path:
SELECT /*+ index(RESPONSE_ACTIVITY_LOG RESPONSE_ACTIVITY_LOG_IX) */ count(master_incident_id)
FROM cad.RESPONSE_ACTIVITY_LOG
Explain plan is:
Operation Object Name Rows Bytes Cost Object Node In/Out
SELECT STATEMENT Optimizer Mode=CHOOSE 1 23485
SORT AGGREGATE 1 5
TABLE ACCESS FULL CAD.RESPONSE_ACTIVITY_LOG 21 M 101 M 23485
Here's some info on index columns/stats and table stats (statistics were updated this morning):
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
RESPONSE_ACTIVITY_LOG_IX MASTER_INCIDENT_ID
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- ----------- ------------- ----------------- ---------- -----------
RESPONSE_ACTIVITY_LOG_IX 2 47378 3284062 3589853 21330703.6 472735
TABLE_NAME NUM_ROWSB BLOCKS EMPTY_BLOCKS CHAIN_CNT
------------------------------ ---------- ---------- ------------- ----------
RESPONSE_ACTIVITY_LOG 21347465 244179 1580 0
Given that the field I want to count is the index column and the fact that it must surely be faster to read thru the 47,378 index blocks as opposed to the 244,179 data blocks, why would this query choose Full table scan?
Thanks,
Sheila