Re: CBO and index usage

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 - 12:35:31 CDT