Re: Newbie seeking help on performance tuning - Oracle 7.3 Server, NT4, Dual PPro 180, 64Meg

lao2_at_total.net wrote:
<snip>
> select * from table where first_name like '%john%'
>

<snip>

Ok, let's pretend you are oracle and your index is a dictionary of names in alphabetical order. How are you going to look up a first_name that begins with anything, has 'john' in the middle, and ends with anything? See the problem? Since indexes are based on sorted order, you must have at least the first character of the name for the index to help. If the statement was changed to "like 'john%', then you can immediately find all the names that begin with 'john' since they are all together in sorted order. But %john% would match 'littlejohn', 'bigjohn','john','johnny',etc.,etc. So indexes can only be used if the first character is known. And 9 seconds for a full table scan isn't all that bad if you really do need to do the %john% thing.

---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.