Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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
    
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:

SELECT foo_id FROM foo WHERE foo_id IS NOT NULL
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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