Re: Upper() and Indexing

Once any function is applied to a column value, no indexes will be used on it. This sounds a shame but you can use it to your advantage when tuning your SQL. Sometime, an unpreferred(say) index is used instead of the index you require when executing your query. You can apply functions to values to force an index not to be used leaving the SQL to pick up your index of choice. you may often find things like

WHERE col || '' = othercol

or

WHERE col+0 = othercol

which, from my experience, seem to be pretty standard mechanisms for forcing an index not to be used.

HTH
--
Alan D. Mills

Luke Gentry wrote in message <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?
>
>Thanks in advance.
>Luke Gentry
Received on Thu Jun 25 1998 - 08:57:57 CDT