Re: interest of indexes ....

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.

Tim Received on Mon Jun 17 2002 - 18:05:06 CDT