Re: Case Insensitive searches

Craig Burkett wrote:

> Hi all,
>
> I need some guidance as to how best accomplish a case insensitive search
> while still using indexes. One thing I was wondering was does PL/SQL
> support regular expressions and can they be applied to existing indexes.
> I'm trying to avoid having to rebuild indexes forcing them to using upper
> case and I certainly don't want a function (ie UPPER) on the field side of
> the equation which would cause a table scan instead of an index search.
>
> Also does anyone know if Oracle 9i support a switch to use case insensitive
> comparisons?

You want to check out function-based indexes. The situation you describe is the example described in the documentation and actually has demo/sample code attached if you search the docco. The search keywords are "function-based indexes". In Oracle9i Release 2, one of the better places to look is the Performance Tuning Guide and Reference, chapter 4.

(Aside: please avoid cross-posting within the comp.databases.oracle.* groups as the same group of people read & respond to all 3 newsgroups.) Received on Fri May 23 2003 - 13:17:50 CDT