SQL/Oracle: when indexes on multiple columns can be used

Question

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?

Answer 1

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)

Answer 2

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)

Answer 3

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

Answer 4

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.