Getachew -- Thanks for the question regarding "Making Oracle Case insensitive ", version 9.2.0.4

Submitted on 12-Mar-2004 11:57 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 22-May-2007 9:26

You Asked

Tom:
    First and foremost, thank you for the great work you do for
the Oracle community.

My question is:

- We have a third party application we use for selecting specific
values from several tables. The character values stored in a field could be in any of the 
following formats:
"Bell", "bell" , "BELL" , "beLL" etc..
1) Is there a way to force (change) values (either to UPPER or Lower case) during a DML 
other than using triggers?  
if not,
2) Is there a way to force a select query (other than using functions UPPER or LOWER) to 
return all the values regardless of the case that a user enters? 

Thank you.

and we said...

1) no

2) in 10g, yes.

ops$tkyte@ORA10G> create table t ( data varchar2(20) );
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t values ( 'Hello' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'HeLlO' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'HELLO' );
 
1 row created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index t_idx on
  2  t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );
 
Index created.
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> variable x varchar2(25)
ops$tkyte@ORA10G> exec :x := 'hello';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where data = :x;
 
no rows selected
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter session set nls_comp=ansi;
 
Session altered.
 
ops$tkyte@ORA10G> alter session set nls_sort=binary_ci;
 
Session altered.
 
ops$tkyte@ORA10G> select * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace on
ops$tkyte@ORA10G> select /*+ first_rows */ * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=12)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

Reviews

Making Oracle Case insensitive March 13, 2004 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Getachew G. from Minneapolis, MN USA

Thank you again.

I will test and implement this in 10G in a few months.
I am not sure what the impact will be if I set the 
two parameters(nls_comp and nls_sort) at the system
level.  But, thank you for providing me with a good information.

Followup March 14, 2004 - 9am Central time zone:

it'll make everything case insensitive will be the impact.  Not sure I'd do it system wide, but via 
alter sessions in the app or via an on logon trigger -- sure.

no caveat ?! March 14, 2004 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Gabe

Regarding #2 and the answer "in 10g, yes" ...

My observations/questions:
1. I'm assuming statistics were gathered automatically (10g feature)
2. Is RBO still available in 10g?
3. I know Cardinality is an estimation ... but why wouldn't the optimizer estimate on the side of 
caution with "Card=<#rows in table>" (or some function of it) rather than "Card=1"?
4. What was the point of first_rows? For the method to be deterministic, the FBI has to be used 
(hints are just ... hints). In fact, any query against a table with 3 rows should probably result 
in a FT ... here the FT would translate in a bug in the application.

I'm highly suspicious of relying on indexes for business logic when querying Oracle (for DML? ... 
yes, FTIs are very useful as you show in your last book) ... is my suspicion unfounded? Can Oracle 
really be forced to always use an index (FBI or not)?

Thanks.

Followup March 14, 2004 - 2pm Central time zone:

1) no, there weren't any 

2) yes, but not supported and not by default

3) small table.  forced the cbo to guess with the hint so the index would be used.

4) to bias the index, table was too small to use an index "for real" otherwise. I was trying to 
demonstrate that "yes, this case insensitive stuff can be as efficient as case sensitive, given the 
correct indexes are in place"

I'm not relying on the index for anything here.  I was merely demonstrating that the index can in 
fact be used transparently.

ops$tkyte@ORA10G> select /*+ FULL(t) */ * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=12)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=1 Bytes=12)


Full scans work just as well.  I would never suggest on relying on a query plan for getting data 
"just so".  Either plan works the same (although they could return the data in very different order 
of course! but if you need a certain order, that is what order by is all about)

indeed ... March 14, 2004 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Gabe

"I'm not relying on the index for anything here"
I can see now you don't.

So it is all about these:
  alter session set nls_comp=ansi;  --works in 9i
  alter session set nls_sort=binary_ci;

Guess the "binary_ci" is the thing that makes this a 10g solution only. I'll look it up in the 
docs.

Thanks.

Followup March 14, 2004 - 7pm Central time zone:

binary_CaseInsensitive
       ^   ^

March 14, 2004 - 11pm Central time zone

Bookmark | Bottom | Top

Reviewer: Bhagat Singh from India

Hello Tom,
may be most of the peoply might not be having 10g right now but I was astonished to see the answer, 
I was expecting some function based indexes and all any special reason for this.

regards
Bhagat Singh

Followup March 15, 2004 - 7am Central time zone:

curious -- you did see a function based index:

ops$tkyte@ORA10G> create index t_idx on
  2  t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );


but any special reason for what exactly?

LIKE operator for case-insensitive queries March 15, 2004 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Gary from Sydney, Australia

Haven't got 10g, so cannot verify this, but it has been reported on another website
(

http://www.quest-pipelines.com/pipelines/dba/index.asp

, that while = (and presumably <, > etc) all pick up on this case-insensitive setting, the LIKE 
operator does not.

Also changing the nls_sort parameter means you cannot mix case-sensitive and case-insensitive 
predicates in a query.
This means you have a choice of 
1) Have both case-sensitive and case-insensitive indexes
2) Risk some queries not have the correct sensitivity index available on some predicates 
3) Choose whether the database as a whole should be case-sensitive or insensitive, and design your 
indexes accordingly

Personally, I still prefer the 'old-tech' UPPER(col)='...' with a function based index on the 
UPPER(col).
That allows the case-insensitivity to be applied at the lowest level so has more flexibility. 
For example, with a table with Address, City, State and Zip Code, you could decide to be 
case-insensitve for Address, but case-sensitive for state because you store them all in upper case.

Followup March 15, 2004 - 6pm Central time zone:

correct but an Oracle Text index can (even in 9i, 8i) do that.

non 10g solution July 9, 2004 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Dez from Netherlands

Recently I encountered the same problem and we couldnt use Function Based Indexes or ad an extra 
index. The way Oracle Forms translates Queries on Case Insensitive fields gives another option:

select * from t where lower(data)='hello' and (data like 'h%' or data like 'H%')

This way the query will still run alot faster without using Funtion Based Index

Hopefully it will be usefull.

an approach for oracle 9 August 12, 2004 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Henry Rabinowitz from New York, NY

I read in the oracle 9 documentation that one can get case insensitive comparison by saying
  alter session set NLS_COMP=ANSI;
  alter session set NLS_SORT=GENERIC_BASELETTER;
This does appear to work for queries like
  select * from emp where ename = 'miller'
But it does not appear to work with queries using LIKE, such as
  select * from emp where ename like 'mill%'

This does not work for LIKE operators August 13, 2004 - 12pm Central time zone

Bookmark | Bottom | Top