views:

2533

answers:

4

If I create an index on columns (A, B, C), in that order, my understanding is that the database will be able to use it even if I search only on (A), or (A and B), or (A and B and C), but not if I search only on (B), or (C), or (B and C). Is this correct?

A: 

That is correct, it will do a seek if you specify A and a scan otherwise(this will also depend on the selectivity of the index)

SQLMenace
This isn't the whole story. If you just specify B the index can still be used if the optimizer chooses to use a "skip scan".
Nick Pierpoint
This answer is incorrect. There are three index access methods that can still be used. See my answer.
David Aldridge
+3  A: 

That is not correct. Always best to come up with a test case that represents your data and see for yourself. If you want to really understand the Oracle SQL Optimizer google Jonathan Lewis, read his books, read his blog, check out his website, the guy is amazing, and he always generates test cases.

create table mytab nologging as (
select mod(rownum, 3) x, rownum  y, mod(rownum, 3) z from all_objects, (select 'x' from user_tables where rownum < 4)
);

create index i on mytab (x, y, z);

exec dbms_stats.gather_table_stats(ownname=>'DBADMIN',tabname=>'MYTAB', cascade=>true);

set autot trace exp

select * from mytab where y=5000;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   INDEX (SKIP SCAN) OF 'I' (INDEX) (Cost=1 Card=1 Bytes=10)
Ethan Post
+2  A: 

Up to version Oracle 8 an index will never be used unless the first column is included in the SQL.

In Oracle 9i the Skip Scan Index Access feature was introduced, which lets the Oracle CBO attempt to use indexes even when the prefix column is not available.

Good overview of how skip scan works here: http://www.quest-pipelines.com/newsletter-v5/1004_C.htm

stuandgravy
+6  A: 

There are actually three index-based access methods that Oracle can use when a predicate is placed on a non-leading column of an index.

i) Index skip-scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105

ii) Fast full index scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044

iii) Index full scan: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107

I've most often seen the fast full index scan "in the wild", but all are possible.

David Aldridge