Re: Upper() and Indexing

Luke Gentry ¼¶¼g©ó¤å³¹ <35917FBF.9A382AA3_at_mindspring.com>...
>Someone has suggested to me that using the UPPER(column_name) function
>in the where clause of an Oracle query will force Oracle to ignore an
>index placed on column column_name. For example, in the following query
>there is an index on CORE_SITE_NAME.SITE_NAME. If it is true that using
>the column in the UPPER() function forces Oracle to ignore the index,
>the index on the SITE_NAME column would be ignored in the query.
>
>SELECT CORE_SITE_NAME.SITE_ID
> FROM CORE_SITE_NAME
> WHERE UPPER(CORE_SITE_NAME.SITE_NAME) = 'ACME' ;
>
>Can anyone confirm / refute the claim that the index on the SITE_NAME
>column would not be used in the above query?

It is true. Any change to the value of indexed columns will prevent the optimizer from
using the index. The query will perform a "TABLE ACCESS FULL" of the CORE_SITE_NAME table. You can use the command "EXPLAIN PLAN" to verify that the Oracle optimizer will ignore the index. Received on Thu Jun 25 1998 - 08:03:54 CDT