Re: Function based index!

I'm not sure everything I'm going to write applies to 10g since I work mainly on 9i.

Oracle usually doesn't use indexes when functions are used on fields. So I would advise to have both the index and the function based index.

Another solution is to store the data in uppercase and to modify the SQL to look to upper('abc') everytime(if possible of course). One less index to take care of, it can be useful if your table is transaction intensive.

Yet another solution(if you dont have access to function based index for whatever reasons), you can store your field value uppercase in another column and simply index this column (and use it when you want to query uppercase).

My opinion for your case : look at the explain plan for "Select some_field from some_table where upper(some_field)=upper('abc')" before adding any index(I suppose you already have an index on some_field).
If it shows table access full, you will need a function based index(or any other solution I indicated before).

Regards,

Hans a écrit :

> I have several applications which executes SQL statements where the where
> clause sometimes is forced to use upper case.
>
> Select some_field from some_table where some_field='abc'
>
> Select some_field from some_table where upper(some_field)=upper('abc')
>
> (criteria may also be "where some_field like 'abc%'.....")
>
> Questions:
> 1. Since both queries above is execeuted pretty often (of course the search
> condition 'abc' is changed) should I create two indexes on the same column
> (some_field in this case) where one is an upper case index so Oracle always
> can use an index?
> 2. Is function-based index supported in standard edition or only in
> enterprise edition (only have enterprise edition to test on)?
>
> Platform: Windows 2003
> Oracle 10g (10.2)
>
> Regards
> /Hans
Received on Tue Sep 26 2006 - 08:56:08 CDT