Case insensitive query __oracle in Oracle

ALTER session SET Nls_sort = Binary_ci;
ALTER session SET Nls_comp = linguistic;

ref:http://yangtingkun.itpub.net/post/468/460324

In Oracle, both command and object names are case insensitive because Oracle converts all names and commands to uppercase when the statement is processed.

However, the characters in the string, whether comparison or sorting, are case sensitive. This is the default way in Oracle, but not the only way.

Let's look at a simple example:

Sql> CREATE TABLE T (NAME VARCHAR2 (30));

Table has been created.

Sql> INSERT into T VALUES (' A ');

1 lines have been created.

Sql> INSERT into T VALUES (' a ');

1 lines have been created.

Sql> INSERT into T VALUES (' B ');

1 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> CREATE INDEX ind_t_name on T (NAME);

The index has been created.

Look at the sorting and query results by default:

Sql> SELECT * FROM T-order by NAME;

NAME
------------------------------
A
B
A

Sql> SELECT * from T WHERE NAME = ' A ';

NAME
------------------------------
A

This is the most normal result, and the following changes the default sorting method for the session:

Sql> ALTER session SET nls_sort = Binary_ci;

The session has changed.

Sql> SELECT * FROM T-order by NAME;

NAME
------------------------------
A
A
B

Sql> SELECT * from T WHERE NAME = ' A ';

NAME
------------------------------
A

You can see that by setting the sort method to Binary_ci, the case of the sort is already implemented insensitive, but the query statement is still case-sensitive, and the comparison is further modified as follows:

Sql> ALTER session SET Nls_comp = linguistic;

The session has changed.

Sql> SELECT * FROM T-order by NAME;

NAME
------------------------------
A
A
B

Sql> SELECT * from T WHERE NAME = ' A ';

NAME
------------------------------
A
A

The purpose of the case-insensitive query has now been reached, because the set comparison is based on semantics rather than binary, whereas the language method of a and a is indistinguishable.

Although the goal is achieved, but still to explain, although this implementation of the case is not sensitive to the query, but the implementation of this result is not exactly the same as the phenomenon seen on the surface.

Looking at it from a query, it seems to be just a matter of judging the name, not doing anything with the column, and in fact not, look at the execution plan in this case:

Sql> SET Autot on EXP
Sql> SELECT * from T WHERE NAME = ' A ';

NAME
------------------------------
A
A

Execution plan
----------------------------------------------------------
Plan Hash value:1601196873

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

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

1-filter (Nlssort ("NAME", ' nls_sort= ' binary_ci ') =hextoraw (' 6100 ')
)

Note
-----
-Dynamic sampling used for this statement

Oracle actually operates the column, nlssort the name, and then determines whether or not the target value is judged. Oracle, however, has no other good way of dealing with it, and the conversion of the constants to the right of the equals is at a low cost, but the SQL's judgment condition is changed from equal to in, and the conversion is more likely to change. And find all the other constants that might be converted to the target value, which is much more complicated than converting the columns.

One problem with this approach, however, is that Oracle is unable to use the index, partly because of the operation of the column, but on the other hand, because the index of Oracle is encoded in binary mode. Therefore, this query takes the form of a full table scan.

Sql> SELECT/*+ INDEX (t ind_t_name)/* from T WHERE NAME = ' A ';

NAME
------------------------------
A
A

Execution plan
----------------------------------------------------------
Plan Hash value:1601196873

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

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

1-filter (Nlssort ("NAME", ' nls_sort= ' binary_ci ') =hextoraw (' 6100 ')
)

Note
-----
-Dynamic sampling used for this statement

In this case, consider setting up a function index to solve the problem:

Sql> CREATE INDEX ind_t_l_name on T (Nlssort (NAME, ' nls_sort=binary_ci '));

The index has been created.

Sql> SELECT * from T WHERE NAME = ' A ';

NAME
------------------------------
A
A

Execution plan
----------------------------------------------------------
Plan Hash value:242883967

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| T | 1 | 17 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | Ind_t_l_name | 1 | | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

2-access (Nlssort ("NAME", ' nls_sort= ' binary_ci ') =hextoraw (' 6100 '))

Note
-----
-Dynamic sampling used for this statement