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!
Best Answer
Which tells us nothing about your Oracle Server version unfortunately! The bit below ("Connected to...") is about the server side
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 likeselect distinct foo_id from foo;
(no idea if it actually would though), but you still need to go to the underlying table to answerselect foo_id from foo;
.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 thanindex
eg/*+ CLUSTER(foo) */