Re: Case Sensetive database property

From: Dmitry E. Loginov <dmitry_loginov_at_mtu.ru>
Date: Wed, 21 Nov 2001 22:23:41 +0300
Message-ID: <9tgv5e$1214$1@gavrilo.mtu.ru>

"Sybrand Bakker" <oradba_at_sybrandb.demon.nl> wrote in message news:9jrnvt0ja790vsmt9a7h4snqqcrbeea4sl_at_4ax.com...
> On Wed, 21 Nov 2001 20:44:33 +0300, "AuRoom Group"
> <moiseyev_at_hotmail.ru> wrote:
>
> >Hello ALL!
> >
> >Can anybody help me.
> >I have the problem with the SELECT .. WHERE.
> >I want to know is there a database option that can set CASE (IN)SENSITIVE
> >for WHERE condition for string comparison
skip...
> 1 use where upper(column_name)=upper(:bindvariable)
> will usually result in a full table scan

1a. Developer-like case insensitive query by the indexed column where upper(column_name)=upper(:bindvariable)   and (column_name like upper(substr(:bindvariable,1,1)||'%')

          or column_name like lower(substr(:bindvariable,1,1)||'%')
         )

In some cases this way allow to use index with pretty good selectivity.

> 2 Have redundant columns with the uppercase variant and index those
> columns (usually there is no need at all to allow case-insensitive
> searches on all columns)
> 3 (*Oracle 8i _Enterprise Edition_ only) create a function based index
> on the affected column and set query_rewrite_enabled to true

>
> Regards
>
> Sybrand Bakker, Senior Oracle DBA
>

Dmitry.... Received on Wed Nov 21 2001 - 13:23:41 CST