Type conversion causes the execution plan to not go through the index test case, and the Execution Plan Index

Type conversion causes the execution plan to not go through the index test case, and the Execution Plan Index

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

 

 

Oracle character type and numeric type conversion

Well, what I understand above is that you must avoid implicit conversion every time you filter. If you use a function on the index field or other conversions will cause the index to be unavailable, and the conversion from character type to value type is preferred, if the field you are filtering is of the character type, oracle will never convert you to the numeric type. Your sentence should be relative to other types, I think, such as the date type. So I think both of them should be correct, but it is about two different rules.

I have worked on a project and joined two tables with tens of millions of tables. The results showed that the speed was too slow. The indexes added for the join were read using the execution plan, if the index is not used, the problem lies in implicit conversion. Therefore, the first sentence is correct, and the second sentence is actually not very specific. On the surface, it cannot be seen. I need to understand the internal conversion mechanism of Oracle... I will discuss it with you in a small way...

Two SQL index problems

1. first, you need to understand the storage structure of indexes. These are some indexing techniques that are common to any index, an index is a set of content that uses physical storage and has a logical impact on the base table. The index storage is generally ordered. If you want to compare the two indexes (a, B) of the same table ), the execution plan will select a [1] and B [1 .. n] compare all records, and then a [2] and B [1... n] The number of comparisons is the Cartesian product, which is equivalent to two FOR loops in C.
For (I = 0; I <= n; I ++)
For (j = 0; j <= n; j ++)
{If (a [I]> B [j])
Then "this record shocould be put into memory"
}
As you know, the index and table records are determined based on their binary height in oracle. The higher the binary height, the more I/O records it will go through, the execution plan is worse. If the index record and the base table record are not in the same block, the query time is increased, therefore, in this case, the oracle execution plan will select full table scan to be faster.
2. You are right, but here, the internal conversion of oracle is not based on the priority,
Such as WHERE Ename = 123
However, if the Ename is of the varchar type, it is converted
WHERE TO_NUMBER (ENAME) = 123
Remember, in oracle, all characters are converted to uppercase letters. Generally, oracle considers that the condition you want to search is "sacred". Therefore, only the indexed columns are converted. (In my opinion, for those who disagree, please click it)