Re: RE: Why does Oracle sometimes favor an index based on column position in the index?
Date: Fri, 21 Jan 2011 08:14:02 +0800
Message-ID: <AANLkTinoVvD3hsL5B9F2r=NXeO4hbbcuomNX9ikv=j9=_at_mail.gmail.com>
So there's a query predicate on COL_B while COL_A is for a join and you have two other tables with their own predicates besides the join. Given that COL_B and not COL_A is a predicate IDX01 is logical. Unfortunately COL_A is for a join.
If the optimizer chose to start with another table (we don't have the full query, ORDERED hint and statisics so we can only speculate), the index with COL_A leading just slightly possibly might have been favoured if the expected number of rows to join was low.
Hemant K Chitale
http://hemantoracledba.blogspot.com
sent from my smartphone
On Jan 21, 2011 3:29 AM, "Taylor, Chris David" < ChrisDavid.Taylor_at_ingrambarge.com> wrote:
Yeah good point it is as if Oracle wont 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 Im 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
...
*From:* Michael Moore [mailto:michaeljmoore_at_gmail.com]
*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_at_ingrambarge.com> wrote:
...
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 20 2011 - 18:14:02 CST