Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Index Hint Being Ignored

412426Mar 9 2006 — edited Mar 10 2006
I'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 7 2006
Added on Mar 9 2006
16 comments
1,591 views