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

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@xxxxxxxxxxxxxxx> wrote:

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

...

*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> wrote:

...

Other related posts: