Re: CBO and index usage

Untrue..
when using like 'value%', CBO will use the index, when present, because this is a range scan.
Only using '%value(%)' the index will NOT be used.

Best regards,

Sybrand Bakker, Oracle DBA

Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message news:37C18663.CBD3B3D1_at_edcmail.cr.usgs.gov...
> When using the LIKE statement, Oracle does not use an index even if one
> existed. It has no choice but to do a full table scan. This makes sense
> if you think about it. If I do a SELECT * FROM TABLE WHERE COL1 LIKE
> '%value%'; then the index can't be used cuz the value could fall
> anywhere in the column. It could start the column, end the column or
> fall in between.
>
> HTH
> Brian
>
>
> Jill wrote:
> >
> > I think if you say
> >
> > "cust# > 0 and sales_order like..."
> >
> > then Oracle will read the index. If you put an explicit hint on the
query
> > to use the index, it may basically do the same thing. But as Mr. Bakker
> > says, this isn't necessarily better.
> >
> > usmrall wrote in message <7pn45b$4ho$1_at_nnrp1.deja.com>...
> > >Hello,
> > >I have an index comprising of the following columns
> > >cust#
> > >sales order
> > >line item
> > >order date
> > >and the table has been analyzed . The adhoc query application
> > >can issue a query on any combination of these columns (and in any
order)
> > >when i issue a like query i.e 'where sales order like '<some value>%''
> > >and run an explain plan on it, the results indicate a full table scan.
> > >why doesn't oracle use the index ? it already has an indexed set of
> > >values to choose from.
> > >Also, does this mean that i have to create an index for each
combination
> > >of predicates ?
> > >please help
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Share what you know. Learn what you don't.
Received on Mon Aug 23 1999 - 14:34:17 CDT