都说Oracle存在NUMBER和VARCHAR2类型的隐式转换，严格意义上需要避免，但为何需要避免，从下面的实验进行验证。
1. 创建测试表和索引
create table tn (id number, name varchar2(1));
1. 创建测试表和索引
create table tn (id number, name varchar2(1));
create index idx_tn on tn (id);
create index idx_tn on tn (name);
分别对NUMBER类型的id字段，VARCHAR2类型的name字段创建索引。
分别对NUMBER类型的id字段，VARCHAR2类型的name字段创建索引。
2. 查看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"用的是列索引范围扫描。
SQL> select * from tn where id = '123';
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 = '123'"，Oracle会将字符类型的123转换为NUMBER类型进行比较，此处仍可使用索引范围扫描，说明VARCHAR2->NUMBER的隐式转换，未对索引产生影响。
3. 查看NUMBER->VARCHAR2的隐式转换
3. 查看NUMBER->VARCHAR2的隐式转换
SQL> select * from tn where name = '123';
no rows selected
Execution 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'"使用的是索引范围扫描。
SQL> select * from tn where name = 123;
no rows selected
Execution 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会将数值类型的123转换为VARCHAR2字符类型，和name进行比较，此处用了全表扫描，说明name的列索引失效。
总结：
1. NAME和VARCHAR2之间可以进行隐式转换，其中VARCHAR2->NUMBER不会导致索引失效，NUMBER->VARCHAR2会让索引失效，因此这种隐式转换，是需要注意避免。
2. 之所以VARCHAR2->NUMBER不会让索引失效，我猜测是转换为where id = to_number('123')。NUMBER->VARCHAR2会让索引失效，我猜测是转换为where to_number(name) = 123。
3. 引申知识点，之所以上面id和name使用的是索引范围扫描，是因为建立的是非唯一B树索引，如果是unique索引，则会使用UNIQUE INDEX SCAN的扫描方式。
补充：
经lhrbest的指正，从谓词条件即可看出端倪。
附：
SQL> select * from tn where id = '123';
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=123)
SQL> select * from tn where name = 123;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NAME")=123)
可以看出此处对NAME做了TO_NUMBER转换，导致索引失效。
"where name = 123"，Oracle会将数值类型的123转换为VARCHAR2字符类型，和name进行比较，此处用了全表扫描，说明name的列索引失效。
总结：
1. NAME和VARCHAR2之间可以进行隐式转换，其中VARCHAR2->NUMBER不会导致索引失效，NUMBER->VARCHAR2会让索引失效，因此这种隐式转换，是需要注意避免。
2. 之所以VARCHAR2->NUMBER不会让索引失效，我猜测是转换为where id = to_number('123')。NUMBER->VARCHAR2会让索引失效，我猜测是转换为where to_number(name) = 123。
3. 引申知识点，之所以上面id和name使用的是索引范围扫描，是因为建立的是非唯一B树索引，如果是unique索引，则会使用UNIQUE INDEX SCAN的扫描方式。
补充：
经lhrbest的指正，从谓词条件即可看出端倪。
附：
SQL> select * from tn where id = '123';
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=123)
SQL> select * from tn where name = 123;
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NAME")=123)
可以看出此处对NAME做了TO_NUMBER转换，导致索引失效。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7192724/viewspace-1450919/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
