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.
Please see the blog post here ( http://mfzahirdba.blogspot.com/2010/04/case-sensitiveness.html)


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!