Re: Oracle Index Question
Date: Wed, 16 Jan 2008 09:06:33 -0800 (PST)
On Jan 16, 9:04 am, david <davidho..._at_gmail.com> wrote:
> On Jan 15, 2:49 pm, Jimbo <jamesfer..._at_hotmail.com> wrote:
> > I work primarily in SQL Server and have run across a situatiion in
> > Oracle that's troubling me....
> > I have a query that contains a where clause with 2 restrictions. Both
> > these fields are in a composite index of 3 fields. If I add the third
> > field to the restriction the optimizer uses the index, however if i
> > keep it with just the 2 fields it does a table scan and takes forever
> > unless I put in an index hint to use the composite index. Is this
> > right?..is this a dba issue?
> If the 2 restrictions are against the 2nd and 3rd columns in the
> index, the only option the optimizer has (in using this index) is to
> perform a index skip scan. If you ensure the leading (1st) column in
> the index is specified as one of the restrictions, then the index can
> be used.
IF the CBO is being used; if the RBO (sigh) is being used no such skipscan will occur.
David Fitzjarrell Received on Wed Jan 16 2008 - 11:06:33 CST