Execution plan doesn't use clustered index!

Question

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!

Answer 1

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) */

Answer 2

I'm pretty sure you're using the MS SQL Server terminology for "clustered" here. This is quite different from the cluster concept in Oracle.

If I'm right then you're just wondering why your regular B*Tree index is not used to answer your query. It is probably because your column foo_id is nullable, therefore some rows may not be indexed and the optimizer will have to go to the table to be sure to retrieve all rows.

You should be able to get an index (fast-full) scan with a query like this:

SELECT foo_id FROM foo WHERE foo_id IS NOT NULL