Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT

Question

I try to create an Oracle 12c Database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production) which ist case insensitive. My first try is just a try to test my aims:

select count(1) from dual where 'a' = 'A';
--0 case sensitive
alter session set nls_sort = 'BINARY_CI';
alter session set nls_comp = 'LINGUISTIC';
select count(1) from dual where 'a' = 'A';
--1 --> Case insensitive

alter session works fine but leads to massive problems with tablescans. Oracle ignores all indexes, which didn't created as BINARY_CI / LINGUISTIC.

As a solution for the tablescan problem I try to create a Database, which has the apropriate nls_sort and nls_comp setted as I needed. I started DBCA (Database Configuration Assitent) and created a Database with the two INI parameters:

nls_comp=LINGUISTIC
nls_sort=BINARY_CI

The Database created but didn't work as expected:

select count(1) from dual where 'a' = 'A';
--0 case sensitive

After some investigation I found:

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_COMP BINARY
NLS_SORT BINARY
*/
select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_SORT BINARY_CI
NLS_COMP LINGUISTIC
*/

Which means, somehow the NLS Parameter vanished from my Database. I wonder if the reason is the seed Database, which DBCA used as a template. My NLS Parameter were used by the instance but there they have no effect on my query.

Should I create a Database without DBCA? How? ( https://docs.oracle.com/cloud/latest/db121/ADMIN/create.htm#ADMIN11073 ) Or can I change somehow the NLS-Parameter inside the Database?

Regards Dezsö

• Justin Cave: I believe your only option is to apply these settings in each session and to ensure that all your indexes are created (or recreated) with the NLS settings that you want. I don't believe it is possible to create an Oracle database that is case insensitive by default.

Answer 1

Even if you manage to create a database with nls_comp=LINGUISTIC, nls_sort=BINARY_CI (from scratch (Custom Database or manually), not seed or template) , the indexes will be still created without these settings and you have to take specify them manually.

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
PARAMETER VALUE
--------------- ---------------
NLS_COMP LINGUISTIC
NLS_SORT BINARY_CI
SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
PARAMETER VALUE
--------------- ---------------
NLS_SORT BINARY_CI
NLS_COMP LINGUISTIC
SQL> create table t1(id varchar2(1), something char(2000));
Table created.
SQL> insert into t1 select 'a', 'a' from dual connect by level <= 10000;
10000 rows created.
SQL> insert into t1 values ('b', 'b');
1 row created.
SQL> commit;
Commit complete.
SQL> create index t1_i1 on t1(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2');
PL/SQL procedure successfully completed.
SQL> select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';
 COUNT(*)
----------
 1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 34cypvvhnwczh, child number 0
-------------------------------------
select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B'
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 907 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| T1 | 5001 | 10002 | 907 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(NLSSORT("ID",'nls_sort=''BINARY_CI''')=HEXTORAW('6200'))

Index can not be used, hint does not help, and the estimated cardinality is just simply CEIL (NUM_ROWS / NUM_DISTINCT). This time with the proper index:

SQL> create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where id = 'B';
 COUNT(*)
----------
 1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9pvkbfkycz6q4, child number 0
-------------------------------------
select count(*) from t1 where id = 'B'
Plan hash value: 3225262789
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_I2 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("T1"."SYS_NC00003$"=HEXTORAW('6200'))