It is said that Oracle has implicit conversions of number and VARCHAR2 types, which need to be avoided in strict sense, but why it needs to be avoided and validated from the following experiments.
1. Creating test tables and Indexes
CREATE TABLE tn (ID number, name VARCHAR2 (1)), CREATE index idx_tn on TN (ID), CREATE index idx_tn on TN (name);
An index is created for the ID field of type number, and the name field of the VARCHAR2 type, respectively.
2. View the implicit conversion of Varchar2->number
Sql> SELECT * FROM tn where id = 1;no rows selected
Execution Plan----------------------------------------------------------plan hash value:3532270966--------------- ------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0) | 00:00:01 | | * 1 | INDEX RANGE scan| Idx_tn | 1 | 13 | 1 (0) | 00:00:01 |---------------------------------------------------------------------------"WHERE id = 1" is used for column index range scanning.
Sql> SELECT * FROM tn where id = ' 123 '; no rows selectedexecution Plan------------------------------------------------- ---------Plan Hash value:3532270966---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0) | 00:00:01 | | * 1 | INDEX RANGE scan| Idx_tn | 1 | 13 | 1 (0) | 00:00:01 |---------------------------------------------------------------------------
"WHERE id = ' 123 '", Oracle converts 123 of the character type to the number type for comparison, where an index range scan is still used to illustrate the implicit conversion of varchar2->number without affecting the index。
3. View the implicit conversion of number--VARCHAR2
Sql> SELECT * from tn where name = ' 123 '; no rows selectedexecution Plan----------------------------------------------- -----------Plan Hash value:479240418---------------------------------------------------------------------------- ---------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS by INDEX rowid| TN | 1 | 15 | 1 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | Idx_tn_name | 1 | | 1 (0) | 00:00:01 |-------------------------------------------------------------------------------------------"where name = ' 123 ' is used for index range scanning.
Sql> SELECT * from tn where name = 123;no rows selectedexecution Plan------------------------------------------------- ---------Plan Hash value:2655062619--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0) | 00:00:01 | | * 1 | TABLE ACCESS full| TN | 1 | 15 | 2 (0) | 00:00:01 |--------------------------------------------------------------------------
WHERE name = 123, Oracle converts 123 of a numeric type to a VARCHAR2 character type, compared to name, with a full table scan, stating that the column index of name is invalid。
Summary：
1. There is an implicit conversion between name and VARCHAR2, where Varchar2->number does not cause the index to fail, NUMBER->VARCHAR2 invalidates the index, so this implicit conversion is to be avoided.
2. Varchar2->number does not invalidate the index, I guess it is converted to where id = to_number (' 123 '). NUMBER->VARCHAR2 will invalidate the index, I guess it is converted to where to_number (name) = 123.
3. Extension of knowledge points, the above ID and name use index range scan, because the establishment of a non-unique B-Tree index, if it is a unique index, you will use the unique indexes scan method.
Implicit conversion of Oracle
