Re: Strange Optimization in Oracle 8

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Sat, 27 Sep 2003 22:21:22 GMT
Message-ID: <C3odb.20311$ZR1.9408@nwrddc01.gnilink.net>

The use of a function on a column disables the use of an index. Search for index + "not used" in this newsgroup and read about the various reasons
why Oracle ignores index usage.
This question has been answered many a times before.

Anurag

"Ken Reily" <reil0037_at_umn.edu> wrote in message news:f0f32458.0309271340.4933c20f_at_posting.google.com...
> 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???
Received on Sat Sep 27 2003 - 17:21:22 CDT