Re: input parameters

>Create a test table with a few single test row and run explain plan.

Why not?
On my db, it showed that LIKE 'abc%' does an INDEX RANGE SCAN, which I expected. However, it came to a surprise, that even LIKE 'f%f' did a INDEX RANGE SCAN. Then again, if I think about it, it is reasonable.

Is this result guaranteed by oracle, or might it change during future releases, or did it change at a earlier release?

When doing the test, I had the idea that oracle might change the access method when the analyze thing is performed and knows, that most of the rows start for example with foo%. In such a case, searching for LIKE 'foo%' would probably be faster doing full table scan. I must admit that I never analyzed a table (or schema, or index or whatever) and need some more reading in that. Seems to be very exciting, all of a sudden.

Rene

>> >Surely, however, using wildcards in this manner would negate the use
>> >of an index - if applicable. This *may*, depending on the situation,
>> >result in a performance deficit...
>>
>> I used to thing that if I use a wildcard search, and the % is at the
>> right most end, an index can still be used. I.e
>>
>> in
>> WHERE foo LIKE 'abc%'
>> an index on foo can be used, while in
>>
>> WHERE foo LIKE 'a%bc'
>> or
>>
>> WHERE foo LIKE '%abc'
>>
>> the index cannot be used. If someone could give an authorative answer,
>> I'd be very glad.
>>
>> Rene
>>
>> --
>> Rene Nyffenegger
>> rene dot nyffenegger at adp-gmbh dot ch
>>
>>
>
>

-- 
Rene Nyffenegger
rene dot nyffenegger at adp-gmbh dot ch