Why does Oracle sometimes favor an index based on column position in the index?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Jan 2011 12:54:49 -0600
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:
