Execution plan doesn't use clustered index!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID.

I have created a clustered B+tree index on the field foodID of my table foo.

And I do the following:

explain plan set assignment_id='' for select foo_id from foo;

which creates the following plan:

0  SELECT STATEMENT
1 0   TABLE ACCESS FULL FOO

then I add the following hint:

explain plan set assignment_id='' for select /*+ INDEX(foo) */ foo_id from foo;

but still got the same plan without using my index.

I'm sure the index is correctly created, and I also executed analyze table foo compute statistics;. I also tried the above in three different optimization modes: RULE, CHOOSE, ALL_ROWS.

So why doesn't my hint work?
Any ideas would be rally helpful!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production"

Which tells us nothing about your Oracle Server version unfortunately! The bit below ("Connected to...") is about the server side


select foo_id from foo;

The clustered index does not contain a pointer to each row of the table - instead it contains pointers to clusters (groups of rows with the same foo_id). It could in theory be useful for a query like select distinct foo_id from foo; (no idea if it actually would though), but you still need to go to the underlying table to answer select foo_id from foo;.


So why doesn't my hint work

Because it is just a hint - the CBO is clever enough to ignore it if it means full scanning the clustered index and the table instead of just the table

Also the correct hint for a clustered scan is cluster rather than index eg /+ CLUSTER(foo) /

StackExchange Database Administrators Q#9338, answer score: 3