Type conversion causes the execution plan to not go through the index test case

Test Environment Simulation:
SQL> drop table t_col_type purge;
Create Table t_col_type (ID varchar2 (20), col2 varchar2 (20), col3 varchar2 (20 ));
Insert into t_col_type select rownum, 'abc', 'efg' from dual connect by level <= 10000;
Commit;
Create index idx_id on t_col_type (ID );
Set linesize 1000
Set autotrace traceonlydrop table t_col_type purge
*
Error at line 1:
ORA-00942: Table or view does not exist

 

SQL> select * From t_col_type where id = 6;

Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463

--------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 36 | 8 (0) | 00:00:01 |
| * 1 | table access full | t_col_type | 1 | 36 | 8 (0) | 00:00:01 |
--------------------------------------------------------------------------------

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

1-filter (to_number ("ID") = 6)

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

Statistics
----------------------------------------------------------
5 recursive cballs
0 dB block gets
64 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

It seems a bit strange here. Index [Create index idx_id on t_col_type (ID);] is clearly created, but why is it not used?

--- Check whether the table has an index
SQL> select index_name, table_name, column_name from all_ind_columns where table_name = 't_ col_type ';

Index_name
------------------------------------------------------------
Table_name
------------------------------------------------------------
Column_name
--------------------------------------------------------------------------------
Idx_id
T_col_type
ID

---- View the table structure
SQL> DESC Scott. t_col_type
Name null? Type
-----------------------------------------------------------------------------
Id varchar2 (20) ---------- note the character type here
Col2 varchar2 (20)
Col3 varchar2 (20)

Pay attention to the predicates in the execution plan again:
1-filter (to_number ("ID") = 6) ---------- type conversion occurs here

Therefore, the existing indexes cannot be used in the execution plan. How can we make the indexes take place correctly?

 

Select * From t_col_type where id = '6'; ------ note that the difference here is enclosed in single quotes, indicating that this is a character,

Execution Plan
----------------------------------------------------------
Plan hash value: 3998173245

Bytes ------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes ------------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 36 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | t_col_type | 1 | 36 | 2 (0) | 00:00:01 |
| * 2 | index range scan | idx_id | 1 | 1 (0) | 00:00:01 |
Bytes ------------------------------------------------------------------------------------------

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

2-access ("ID" = '6 ')

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

Statistics
----------------------------------------------------------
9 recursive cballs
0 dB block gets
39 consistent gets
1 physical reads
0 redo size
640 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

 

 

Type conversion causes the execution plan to not go through the index test case