Oracle implicit conversion, Oracle implicit conversion

Oracle implicit conversion, Oracle implicit conversion
It is said that Oracle has implicit conversions of the NUMBER and VARCHAR2 types, which must be avoided in a strict sense, but why should we avoid them? We will verify it from the following experiment.

1. Create 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 );
Create indexes for the id field of the NUMBER type and the name field of the VARCHAR2 type respectively.

2. Check the implicit conversion of VARCHAR2-> NUMBER.
SQL> select * from tn where id = 1; no rows selected
Execution Plan -------------------------------------------------------- Plan hash value: 3532270966 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 | * 1 | index range scan | IDX_TN | 1 | 1 3 | 1 (0) | 00:00:01 | ------------------------------------------------------------------------- "where id = 1" is used for column index range scanning.

SQL> select * from tn where id = '000000'; no rows selectedExecution Plan against Plan hash value: 123 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 = '000000'", Oracle converts 123 of the character type to NUMBER for comparison. Here, index range scanning can still be used, indicating implicit conversion of VARCHAR2-> NUMBER, index not affected.

3. View NUMBER-> VARCHAR2Implicit conversion
SQL> select * from tn where name = '000000'; no rows selectedExecution Plan against Plan hash value: 123 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | interval | 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 | average "where name = '000000'" uses index range scan.

SQL> select * from tn where name = 123; no rows selectedExecution Plan against Plan hash value: 2655062619 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 the value type to the VARCHAR2 character type and compares it with name. A full table scan is used here, indicating that the column index of name is invalid..

Summary:
1. implicit conversion can be performed between NAME and VARCHAR2, where VARCHAR2-> NUMBER does not cause index failure, and NUMBER-> VARCHAR2 will invalidate the index. Therefore, this implicit conversion should be avoided.
2. The reason why VARCHAR2-> NUMBER does not invalidate the index is that it is converted to where id = to_number ('123 '). NUMBER-> VARCHAR2 will invalidate the index. I guess the conversion is where to_number (name) = 123.
3. extended knowledge points: The above id and name use INDEX range scanning because they are created with a non-unique B-tree INDEX. If they are UNIQUE indexes, the unique INDEX SCAN method is used.