Re: Strange Optimization in Oracle 8

Ken Reily wrote:

>I am getting (what I think are) strange results from the optimizer on
>the Oracle server that I am using. I'll try and simplify the problem
>for posting here. I have a table, Person, containing columns id and
>name (again, very simplified). I CREATE INDEX Person_name ON Person
>(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???
>
>

If your version supports it build a function based index on the SUBSTR.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)