Re: Oracle Index Question

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.
>
> Dave

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