Function based index!

From: Hans <hans_at_nospam.com>
Date: Tue, 26 Sep 2006 12:06:45 +0200
Message-ID: <efau3m$nbs$1@emma.aioe.org>

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 - 05:06:45 CDT