Re: Case insensitive query in SQLPlus?

In Oracle 8i and above ( at least ) you can create an index that uses a function ( Like Upper)

To create a 'normal' index you would :
create index I_myindex_name on employee (empl_nm);

( This indexes the empl_nm field in the Employee table - as it exists ( whatever case it is in)

A function-based index statement :
create index I_myindex_upname on employee upper(empl_nm); This creates an index on the empl_nm field BUT in upper -case so that a where clause that specifies Upper(empl_nm) will use it...

stanb_at_panix.com (Stan Brown) wrote:

>In <hVya7.417774$p33.8440380_at_news1.sttls1.wa.home.com> "Jim Kennedy" <kennedy-family_at_home.com> writes:
>
>>You can make a function based index (upper( theColumn)) and then it will be
>>much faster than doing upper on both sides of the where clause.
>>Jim
>
>I'm sorry, I'm not following you here. Perhaps a small example, might make
>it clearer. I don;t know aht you mean by "a function basde index".
>
>Thanks.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Fri Aug 03 2001 - 14:03:00 CDT