1

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ö

1
  • 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. Jul 16 '15 at 17:41
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'))
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.