在文章《【问题处理】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 --
本文将展示“隐式转换”直接带给我们的弊端——索引无法得到有效地使用。
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 --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-660638/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-660638/