"Like" will allow an index read. A function, however, such as substr,
to_number, etc, will preclude any index usage.
Brian Peasland wrote in message <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 Tue Aug 24 1999 - 22:33:11 CDT
