RE: Why does Oracle sometimes favor an index based on column position in the index?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Michael Moore' <michaeljmoore@xxxxxxxxx>
- Date: Thu, 20 Jan 2011 13:23:46 -0600
Yeah good point - it is as if Oracle won't use and index when COLUMNA is the
leading field.
Some more info:
TABLEA
COLUMNA - NUMBER(10)
COLUMNB - VARCHAR2(4)
COLUMNC - NUMBER(3)
...
...
...
SQL:
-No portions of TABLEA in the SELECT
-in the WHERE we have:
WHERE TABLEA.COLUMNA = TABLEB.COLUMNA
AND TABLEA.COLUMNB = TABLEC.COLUMNA
AND TABLEA.COLUMNB IN ('X','Y')
...
...
My apologies for the poorly written pseudo-code as I'm only garner some
ideas/thoughts before I dig into a 10053 trace.
Thanks,
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.
From: Michael Moore [mailto:michaeljmoore@xxxxxxxxx]
Sent: Thursday, January 20, 2011 1:15 PM
To: Taylor, Chris David
Subject: Re: Why does Oracle sometimes favor an index based on column position
in the index?
My question would have been: since columna is the primary key and since the
where clause is referencing columna, why is the query not using the primary key?
Mike
On Thu, Jan 20, 2011 at 10:54 AM, Taylor, Chris David
<ChrisDavid.Taylor@xxxxxxxxxxxxxxx<mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx>>
wrote:
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:
