Re: Oracle Index Question

From: <>
Date: Wed, 16 Jan 2008 09:06:33 -0800 (PST)
Message-ID: <>

On Jan 16, 9:04 am, david <> wrote:
> On Jan 15, 2:49 pm, Jimbo <> 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? 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

Original text of this message