Re: RE: Why does Oracle sometimes favor an index based on column position in the index?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: hemantkchitale@xxxxxxxxx, ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Fri, 21 Jan 2011 06:23:41 -0800 (PST)
I have examples here:
http://oratips-ddf.blogspot.com/2008/11/magical-indexes.html
illustrating what Oracle does with a concatenated index using various data
configurations. Note that when the leading column is unique (the first set of
queries in the post) an index skip scan will not occur (which is the situation
here). The only index in the original set to use was IDX01, on COLUMNB.
Creating IDX03 fixed that.
David Fitzjarrell
________________________________
From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
Cc: Michael Moore <michaeljmoore@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Sent: Thu, January 20, 2011 6:14:02 PM
Subject: 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
Other related posts: