Perform case-insensitive queries in Oracle

InOracleThe command and object names are case-insensitive, becauseOracleWhen processing a statement, convert all the names and commands into uppercase letters.

However, the characters in a string, whether compared or sorted, are case sensitive. InOracleIs the default method, but not the only method.

The following is a simple example:

SQL> Create Table T (name varchar2 (30 ));

The table has been created.

SQL> insert into T values ('A ');

Created1Line.

SQL> insert into T values ('A ');

Created1Line.

SQL> insert into T values ('B ');

Created1Line.

SQL> commit;

Submitted.

SQL> Create index ind_t_name on T (name );

The index has been created.

Check 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. Modify the default session sorting method below:

SQL> alter session set nls_sort = binary_ci;

The session has been 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 sorting methodBinary_ci, Which is not case sensitive to sorting, but is still case sensitive in the query statement. The comparison method is further modified as follows:

SQL> alter session set nls_comp = linguistic;

The session has been changed.

SQL> select * from t order by name;

Name
------------------------------
A
A
B

SQL> select * from t where name = 'a ';

Name
------------------------------
A
A

Now we have achieved the case-insensitive query goal. This is because the comparison method is based on semantics rather than binary.AAndAThere is no difference.

Although the goal has been achieved, it should be noted that although the case-insensitive query is implemented here, the implementation of this result is not exactly the same as the phenomenon seen on the surface.

From the query statement, it seems thatNameYou can make a judgment without performing any operations on the column, but this is not the case. Let's 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 ('123 ')
)

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

OracleThe column is operatedNameDoneNlssortOperation, and then determine whether it is consistent with the target value. HoweverOracleThere are no other good methods for processing. It is a low cost to convert the Constants on the right of the equal sign,SQLIs changed from equalIn, I am afraid this type of conversion will change even more. It also finds all other constants that may be converted to the target value. This operation is much more complicated than column conversion.

However, this method has a problem:OracleIndexes cannot be used. On the one hand, columns are operated, and on the other handOracleAccordingBinaryEncoding and storage. Therefore, this query uses the full table scan method.

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 ('123 ')
)

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

In this case, you can consider creating 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

Bytes --------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes --------------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------------

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

2-access (nlssort ("name", 'nls _ sort = ''binary _ ci''') = hextoraw ('123 '))

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