Oracle search queries are case sensitive by default. By changing session parameter NLS_SORT and NLS_COMP parameter, it’s possible to make the search option case insensitive.
Example:
create table employee(id number primary key,name varchar2(100 char));
create index idx_emp_name employee(name);
Insert into employee(id,name) values(1,'James');
Insert into employee(id,name) values(2,'Scarfield');
Insert into employee(id,name) values(3,'Diana');
Insert into employee(id,name) values(4,'dIana');
SQL> select * from employee where name='Diana';
ID NAME
---------- ------------------------------
3 Diana
SQL> select * from employee where name='DiAna';
no rows selected
So the case insensitive search is not working.
SQL> explain plan for select * from employee where name='Diana';
Explained.
SQL> select * from TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2641253456
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 215 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 215 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
You can check the default value of NLS_SORT and NLS_COMP by executing following query:
SQL> select * from nls_database_parameters
where parameter in('NLS_SORT','NLS_COMP');
Now set the parameter:
SQL>alter session set NLS_COMP=LINGUISTIC;
SQL>alter session set NLS_SORT=BINARY_CI;
SQL> select * from employee where name='diANa';
ID NAME
---------- ------------------------------
3 Diana
4 dIana
Now the case insensitive search are working. Now check the explain plan.
SQL> explain plan for select * from employee where name='Diana';
Explained.
SQL> select * from TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 215 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE | 1 | 215 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
The execution plan here completely changes since the index in not case insensitive. So this parameter should not be set in global level. It can be set session level based on requirement otherwise there could be huge performance impact. Especially this parameter invalidate varchar2 type case sensitive index. You can create case insensitive index based on requirement.
SQL> create index cis_idx_emp_name on employee (NLSSORT (name, 'NLS_SORT=BINARY_CI') );
Index created.
SQL> explain plan for select * from employee where name='Diana';
Explained.
SQL> select * from TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 4238178907
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1217 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 1217 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CIS_IDX_EMP_NAME | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Now the case insensitive index are using by the query.