"Tim Cross" <tcross_at_pobox.une.edu.au> wrote in message
news:87660h1p7h.fsf_at_blind-bat.une.edu.au...
> Fred Burlaud <fburlaud_at_com6.fr> writes:
> >
> > For ex. I would like to know if an index on phone field is
> > appropriated in this case
> >
> > SELECT * From Contact WHERE phone like '%0867%';
> >
> No an index won't work in the above situation - if you think about it,
> what benefit could an index be when your search string starts with a
> wildcard - at what point in the index would you begin to search?
>
> At a theoretical level, it is possible that an index might be useful
> in situations where your search string is something like 'abcd%'
> because you could use the index to narrow down the search space to
> only indicies which begin with 'abc'. However, when the string is like
> '%abc', you would have to search all index positions - this could
> result in even more I/O than just scanning the table itself.
>
> I don't know if Oracle will use indicies if the wildcard is at the end
> of the search string or not, but I'm fairly confident in saying it
> won't if the wildcard is at the beginning.
It may (at least in 817 and above) if the wild card is at the end -provided
the inmdex search makes sense to the optimiser.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Thu Jun 20 2002 - 14:37:44 CDT
