【隐式转换】注意隐式转换将导致索引无法使用

2020-02-06 23:24:07

在文章《【问题处理】ORA-01722: invalid number》（http://space.itpub.net/519536/viewspace-660522）中描述了一个有关隐式转换的隐蔽的问题。
本文将展示“隐式转换”直接带给我们的弊端——索引无法得到有效地使用。

1.创建测试表T并初始化几行字符串内容
sec@ora10g> create table t (x varchar2(10));

Table created.

sec@ora10g> insert into t values ('1');

1 row created.

sec@ora10g> insert into t values ('2');

1 row created.

sec@ora10g> insert into t values ('3');

1 row created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t;

X
----------
1
2
3

2.在表T的X字段创建索引I_T
sec@ora10g> create index i_t on t(x);

Index created.

3.开启autotrace功能跟踪SQL语句的执行计划
sec@ora10g> set autotrace traceonly explain

4.正常情况下，索引可被使用
sec@ora10g> select * from t where x = '1';

Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825

-------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     1   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T |     1 |     7 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"='1')

Note
-----
   - dynamic sampling used for this statement

5.验证发生隐式转化时索引无法被使用
去掉“1”前后的单引号，再次执行上述SQL语句，此时字符串类型的X字段将与一个数字进行匹配，此时会发生隐式转换。
sec@ora10g> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=1)

Note
-----
   - dynamic sampling used for this statement

可见，在隐式转换发生的情况下，索引时无法被使用到的。

6.小结
如果在程序开发过程中出现隐式转换类型的SQL，在生产环境中将是一个较大的隐患。在数据量逐渐增大的系统中该问题将会渐渐的凸显。
应对策略很简单，针对具体字段类型赋予与之相符的值。

Good luck.

secooler
10.04.23

-- The End --

分享好友

分享这个小栈给你的朋友们，一起进步吧。