Case Sensitiveness — Part 2

Oracle maintains the case sensitiveness by default.

Prior to Oracle 12c Release 2 , we can achieve the nls settings at the session level. With Oracle 12c Release 2 , we can set it at the column level , table level and of course at the session level. Column level settings takes precedence over other settings .

Here is the example .

Let us create the table in a traditional way .

SQL> CREATE TABLE empl_reg (
 2 firstname VARCHAR2(30) ,
 3 lastname VARCHAR2(30) ,
 4 sal INT
 5 );

Table created.

SQL> insert into empl_reg values ( 'Zahir','mohideen', 1000 ) ;

1 row created.

SQL> insert into empl_reg values ( 'zahir','mohideen', 2000 ) ;

1 row created.

SQL> insert into empl_reg values ( 'zAhir','mohIdEen', 5000 ) ;

1 row created.

SQL> insert into empl_reg  values ( 'Abdul','MohIdEen', 50000 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL>  Select * from empl_reg where firstname ='zahir';

FIRSTNA LASTNAME            SAL
------- ------------ ----------
zahir   mohideen           2000

In this case , to get all records with First name as ‘Zahir’ , as a workaround , we typically use lower/upper function. Sometimes , we will use Function Based Index on this functions to boost the performance.

SQL> Select * from empl_reg where lower(firstname) =’zahir’;

FIRSTNA LASTNAME SAL
 - - - - - - - - - - - - - - - 
Zahir mohideen 1000
zahir mohideen 2000
zAhir mohIdEen 5000

Now , let us create the table with columns — collation case insensitive ( ci) way .

SQL> Create table emp
 2 ( firstname varchar2(30) collate binary_ci ,
 3 lastname varchar2(30) collate binary_ci ,
 4 sal int
 5 ) ;

Create table emp
*
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

You would need to set the parameter to extended at the CDB , PDB SEED and PDBs and bounce the instance , as this is a static parameter.

This is a bit tedious workflow . Hopefully , in the next release , this process is made simpler .

SQL> CREATE TABLE empl (
 2 firstname VARCHAR2(30) COLLATE binary_ci,
 3 lastname VARCHAR2(30) COLLATE binary_ci,
 4 sal INT
 5 );

Table created.

SQL> insert into empl values ( 'Zahir','mohideen', 1000 ) ;

1 row created.

SQL> insert into empl values ( 'zahir','mohideen', 2000 ) ;

1 row created.

SQL> insert into empl values ( 'zAhir','mohIdEen', 5000 ) ;

1 row created.

SQL> insert into empl values ( 'Abdul','MohIdEen', 50000 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> Select * from empl where firstname ='zahir';

FIRSTNA LASTNAME            SAL
------- ------------ ----------
Zahir   mohideen           1000
zahir   mohideen           2000
zAhir   mohIdEen           5000 

Now , we can get all the three records no matter what the case is . As we can see, the explain plan does nlsssort .

SQL> explain plan for Select * from empl where firstname =’zahir’;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3277272027

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    47 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPL |     1 |    47 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(NLSSORT("FIRSTNAME",'nls_sort=''BINARY_CI''')=HEXTORAW('7A
              6168697200'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

SQL> explain plan for Select * from empl_reg where firstname ='zahir';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1040152178

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    47 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPL_REG |     1 |    47 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("FIRSTNAME"='zahir')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

Comments Welcome!

Originally published at https://zahirmohideen.blogspot.com/2018/04/case-sensitiveness-part-2.html