Re: Upper() and Indexing

It is true. The index is on the actual values in the column and it is case-sensitive. You can either store your data in the column in all upper case, or of displaying in mixed case is a concern, create an upper case search column and a mixed case display column, ie:

ELECT CORE_SITE_NAME.SITE_NAME_DISPLAY
     FROM CORE_SITE_NAME
 WHERE CORE_SITE_NAME.SITE_NAME_SEARCH = 'ACME' ; It doubles your storage for those columns, but it works.

--
Tom Mettling
WT Chen & Company, Inc.
mettling_at_volpe.dot.gov

Luke Gentry wrote:

> 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?
>
> Thanks in advance.
> Luke Gentry
Received on Thu Jun 25 1998 - 12:09:55 CDT