SQL / Oracle: when you can use indexes on multiple columns

Question

If I create an index in columns (A, B, C), in that order, I understand that the database can use it even if I search only on (A) or (A and B), or (A and B and C), but not if I only search for (B) or (C) or (B and C). Is it correct?

Answer 1

There are actually three index-based access methods that Oracle can use when a predicate is placed in an intransitive index column.

i) Scanning by index: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105

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

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

I often saw a quick scan of the full index in the wild, but all of this is possible.

Answer 2

This is not true. It is always best to come up with a test case that presents your data and sees for yourself. If you want to truly understand the Oracle SQL Optimizer google Jonathan Lewis, read his books, read his blog, look at his website, the guy is awesome, and he always creates 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

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

Oracle 9i introduced the Skip Index Access feature, which allows Oracle CBO to attempt to use indexes even if the prefix column is not available.

A good overview of how scanning works: http://www.quest-pipelines.com/newsletter-v5/1004_C.htm