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:
- » Why does Oracle sometimes favor an index based on column position in the index? - Taylor, Chris David
- » RE: Why does Oracle sometimes favor an index based on column position in the index? - Taylor, Chris David
- » Re: Why does Oracle sometimes favor an index based on column position in the index? - Martin Berger
- » Re: Why does Oracle sometimes favor an index based on column position in the index? - David Fitzjarrell