Re: input parameters

In software nothing is guaranteed except the software company will charge money ... and we or out customers and/or employers will pay money.

Everything else is subject to change without notice.

Daniel A. Morgan

Rene Nyffenegger wrote:

> >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
>
>
Received on Fri Apr 06 2001 - 00:21:26 CDT