Re: Strange Optimization in Oracle 8

"Ken Reily" <reil0037_at_umn.edu> wrote in message news:f0f32458.0309271340.4933c20f_at_posting.google.com...

> (name). Then, to find all names beginning with K, I use this query:
>
> SELECT name FROM Person WHERE substr(name,1,1) = 'K'
>
> This query does a full table scan! Shouldn't it use the index? The
> equivalent query:
>
> SELECT name FROM Person WHERE name LIKE 'K%'
>
> uses the index. Any thoughts???

Functions applied to columns in predicates disable index use. The first query has a better chance of using the index if you code it as:

SELECT name FROM Person WHERE name like 'K%'

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam