Oracle set case Sensitive __oracle

(The following content is valid only in the case of "=", like not.) ）

Sometimes we do not want to query the lower or upper and other functions and the contents of the case are queried, such as from SQL Server, MySQL and other transfer to Oracle. The oracle10g can be implemented by modifying the session:

ALTER session SET Nls_comp=ansi;

ALTER session SET Nls_sort=binary_ci;

Analytical:

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, Nls_com adds a new value linguistic setting this value to make the case insensitive in Nl_sort. Correspondingly, Nl_sort also adds a value: Binary_ci, (CI or case insensitive), which is not case-sensitive.

However, after actually setting these two values, it is not true that the case is insensitive, but that Oracle automatically adds the upper function to the statement. See 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 calls
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):
----------