Oracle install is typically case insensitive i.e. a column that has a uniqueness constraint enforced on it can have “RAM”, “ram”, “Ram” or any different upper and lower case combinations in it.
Both SQL Server and Oracle can be made to behave differently (as compared to their default behaviors of CI and CS respectively) and there are multiple ways and multiple levels (instance, database, table, column and query) at which that change can be made in SQL Server. In Oracle 10g, these changes can be made at the session level or database level.
Till Oracle 9i, there was no other choice in Oracle to make it work like in SQL Server other than to ensure that your application handles it correctly (or two kludges which you don’t want to know 
since those have performance implications since one has to use FBI (Function Based Indexes) for all such searches and depending upon how your application is written, it will require application changes as well…and there was one other kludge as well to play with NLS_xxx parameters to hack around it).
An Oracle customer is used to seeing “RAM” as well as “ram” in the same “user_name” column which has a uniqueness constraint enforced on it, where as in the default CI (case insensitive) install of SQL Server, a SQL server customer would expect only a single record.
In Oracle, the NLS change can be made at the database level but if you have multiple applications using different schemas on the same Oracle database and require a different behavior, then these changes should not be made at the database level. In 10g, the **elegant** way of handling this is by making changes at the session level but this will effect every search for that session. Let’s take a example to illustrate this behavior at the session level in Oracle:
create table TEST ( COL1 varchar2(100) );
insert into TEST values ( ‘RAM’ );
insert into TEST values ( ‘ram’ );
insert into TEST values ( ‘rAm’ );
alter session set nls_comp=linguistic ;
alter session set nls_sort=binary_ci;
variable a varchar2(25)
exec :a := 'RaM';
SQL> select * from TEST where COL1 like :a;
COL1
——————————————————–
RAM
ram
rAm
SQL> select * from TEST where COL1 like :a || ‘%’;
COL1
——————————————————–
RAM
ram
rAm
SQL> select * from TEST where COL1 = :a ;
COL1
——————————————————–
RAM
ram
rAm
Caveats ?
Yes, there is one. You need to create your indexes using the same NLS_SORT..example: If you create only a normal index :
create index test_ind_1 on test (col1);
And then dump a bunch of records into this table to ensure that the filter criteria is meaningful and that the number of blocks are large enough to warrant an index search criteria…and of course collect the stats:
SQL> SELECT COUNT(1) FROM TEST;
COUNT(1)
———-
232674
SQL> alter session set nls_comp=linguistic ;
Session altered.
SQL> alter session set nls_sort=binary_ci;
Session altered.
SQL> SELECT COUNT(1) FROM TEST WHERE COL1 = ‘RaM’;
COUNT(1)
———-
3
SQL> set autotrace traceonly
SQL> /
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=109 Card=11 Bytes=242)
1 0 TABLE ACCESS (FULL) OF ‘TEST’ (TABLE) (Cost=109 Card=11 Bytes=242)
Now, let’s drop and re-create the index using nls_sort:
drop index test_ind_1;
create index test_ind_1 on test ( nlssort( col1, ‘NLS_SORT=BINARY_CI’ ) );
Begin
Dbms_Stats.Gather_Table_Stats(User,’TEST’,
Method_Opt=>’For Table For All Indexes For All Indexed Columns’,
Cascade=>True);
End;
/
And now, re-try the execution plan:
SQL> /
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=10 Bytes=230)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘TEST’ (TABLE) (Cost=3 Card=10 Bytes=230)
2 1 INDEX (RANGE SCAN) OF ‘TEST_IND_1′ (INDEX) (Cost=1 Card=28)
As you can see from the change in the execution plan above, the plan now uses a range scan instead of a FTS. If at all you have a requirement to have case insensitive behavior in Oracle, you can use these techniques.
