Oracle case sensitivity

(The following content takes effect only when "=" is used, but not if "LIKE" is used .)

Sometimes we do not want to use lower or upper functions to query all case-sensitive content, such as data transferred from sqlserver and mysql
Oracle. You can modify the session at oracle10g:

Alter session set NLS_COMP = ANSI;

Alter session set NLS_SORT = binary_ci;

Resolution:

SQL> select * from v $ version;

BANNER
----------------------------------------------------------------
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL
Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production
NLSRTL
Version 10.2.0.1.0-Production

SQL> select * from test;

ID
--------------------
A
A

SQL> select * from test where id = 'a ';

ID
--------------------
A

SQL> ALTER SESSION SET NLS_COMP = ANSI;

Session altered

SQL> ALTER SESSION SET NLS_SORT = binary_ci;

Session altered

SQL> select * from test where id = 'a ';

ID
--------------------
A
A

SQL> select * from test where id = 'a ';

ID
--------------------
A
A

SQL>

In 10gR2, the value of LINGUISTIC is added to nls_com.
Set this value to make it case insensitive in nl_sort. Correspondingly, nl_sort also adds a value: BINARY_CI, (CI is the Case
Insensitive), that is, it is case Insensitive.

However, after these two values are set, they are not case insensitive, but are equivalent to Oracle automatically adding the upper function to the statement. Take the following example:

SQL> set autot on
SQL> select * from t2 where f1 = 'a ';

F1 AAA
--------------------
A 2

Execution Plan
----------------------------------------------------------
Plan
Hash Value: 2238318762

--------------------------------------------------------------------------------
-------
|
Id | operation | Name | rows | bytes | cost (% CPU) | tim
E |
--------------------------------------------------------------------------------
-------
|
0 | SELECT statement | 1 | 5 | 2 (0) | 00:
00:01 |
| 1 | table
Access by index rowid | T2 | 1 | 5 | 2 (0) | 00:
00:01 |
| * 2 |
Index range scan | t2_idx1 | 1 | 1 (0) | 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate information (identified by Operation ID ):
---------------------------------------------------

2-access ("f1" = 'A ')

Statistics
----------------------------------------------------------
0
Recursive cballs
0 dB block gets
0 consistent gets
0 physical
Reads
0 redo size
0 bytes sent via SQL * Net to client
0 bytes
Received via SQL * Net From Client
0 SQL * Net roundtrips to/from client
0
Sorts (memory)
0 sorts (Disk)
1 rows processed

SQL> alter session set nls_sort = binary_ci;

Session altered.

SQL> alter session set nls_comp = linguistic;

Session altered.

SQL> select * from t2 where f1 = 'a ';

F1 AAA
--------------------
A 1
A 2

Execution Plan
----------------------------------------------------------
Plan
Hash value: 1513984157

--------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
|
0 | select statement | 1 | 5 | 3 (0) | 00:00:01 |
| * 1 | TABLE
Access full | T2 | 1 | 5 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

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