Why does Oracle sometimes favor an index based on column position in the index?

This is something I've always wondered about and I'm looking for ideas (or even 
the answer itself)

We recently ran into a simple situation

TABLE_A has 3 indexes:
-PK: Primary Key on COLUMNA
-IDX01: Non-Unique Index on COLUMNB
-IDX02: Non-Unique Index COLUMNA, COLUMNB, COLUMNC

The WHERE statement of the query referenced COLUMNA and COLUMNB (but NOT 
COLUMNC).

The optimizer chose INDEX01 (COLUMNB) but I would have thought that it would 
have chosen IDX02 and 'ignored' the data in the index for COLUMNC.  The LIOs 
were through the roof as it did INDEX SCAN + TABLE LOOKUP, repeat, repeat, 
repeat and finished in 3 minutes for 12 rows.  I think it 17M LIOs if I 
remember correctly.

I regathered stats on the tables and indexes using a script and it still 
continued to use IDX01.

I created a 3rd index, IDX03 on COLUMNB, COLUMNA (so reverse order of IDX02 and 
no COLUMNC).

Regather stats again and rerun.

LIOs go back to normal and execution completes in less than 2 secs.

So it seems that I have 2 things going on:

1.)    Oracle "prefers" to use an index with too few columns versus 1 that has 
too many?

2.)    Or Oracle prefers to have INDEX columns in a specific order?

(Come to think of it, there is an ORDERED hint in the SQL so maybe the order 
the WHERE clauses were impacting which INDEX Oracle chose?  I think I was under 
the impression the ORDERED hint only impacted the order of the joins)



Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

Other related posts: