I'm using "SQL*Plus: Release - 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:


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!

  • 1
    Welcome to dba.se John. Can you give us a bit more information: What are you trying to achieve with clustering? Did you create the cluster with create cluster or is there a terminology mix up between you and me? – Jack Douglas Dec 18 '11 at 0:49
  • 1
    There is no such thing as a "clustered b-tree index" in Oracle – a_horse_with_no_name Dec 19 '11 at 10:14

I'm using "SQL*Plus: Release - 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) */

  • Jack thank for your answer. Is there a way I can force CBO to use my index? – John Fr. Dec 18 '11 at 0:54
  • 1
    Can you explain why exactly you want it to? In general you can't force the CBO to do something but the hint you'd try is cluster rather than index eg /*+ CLUSTER(foo) */ – Jack Douglas Dec 18 '11 at 1:11
  • I tried that one too... but still didn't use it :( ... anyway thanks for your help – John Fr. Dec 18 '11 at 1:12

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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.