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???

Your first example applies a function to the NAME column. Your function happens to just search for the first charachter, but that's not the point. You could be calculating the square root of the ASCII value for all Oracle knows or care. Point is: as soon as you ask Oracle to evaluate rows by way of a function, you're going to have to scan every row to do the calculations, and you're in to full table scans.

Your second example merely asks Oracle to drop into the index at the K values and scan until it encounters some 'not K' value. "Like" isn't a function in the same way as 'substr' is.

In 8i (and above), you could get Oracle to use an index in your first example by the simple expedient of creating an index on Person (substr(name,1,1)). That's called a function-based index, and is only available in the Enterprise Edition of version 8i and 9i.

Regards
HJR Received on Sat Sep 27 2003 - 18:05:39 CDT