"Jörg Schneider" <oracle_news.3.joesch_at_spamgourmet.com> wrote in message
news:apoaq5$det$1_at_newsreader2.netcologne.de...
> hello together,
>
> we are having a field "name" in our table. Now we want to seach not
> case-sensitive. If we use lowercase of the field and the searchstring, the
> database uses full-table-scan. Is it possible to use the function
lowercase
> (?) in an index on field "name"? We must support ORACLE 7.3 till 9.x.
The only solution that will work across all those versions is to create a
second field and populate it with the data from your 'case insensitive'
field upper (or lower cased) via a trigger. Then do your searches against
the second column. 8i and higher do allow you to create function based
indexes this functionality requires Oracle Enterprise Edition.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Received on Wed Oct 30 2002 - 05:21:03 CST
