Oracle隐式类型转换导致索引失效

Oracle隐式类型转换导致索引失效

隐式类型转换就是Oracle在判断类型是自动将varchar2类型转换为number类型，或者将number类型转换为varchar2类型，隐

式类型转换必须要求是可以转化的，比如varchar2类型的123可以转化为number，但是abc就不可以转换

注：隐式类型转换number转换成varchar2时，索引会失效。varchar2转换为number时，索引并不失效

-- Create table
create table Z_HASH
(
ID VARCHAR2(100) primary key,
NAME VARCHAR2(100)
)

--创建存储过程，插入数据
CREATE OR REPLACE PROCEDURE "ETL_Z_HASH_" IS
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO Z_HASH VALUES(z_hash_id.nextval,'hash-'||z_hash_id.currval);
COMMIT;
END LOOP;

end ETL_Z_HASH_;

--分析下面的语句
select * from z_hash t WHERE t.id='1573';
--'1573'为varchar2类型，没有用到隐式类型转换，分析结果如下：

SQL> explain plan for select * from z_hash t where t.id='200305';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 | 1 (0)
| 1 | TABLE ACCESS BY INDEX ROWID| Z_HASH | 1 | 104 | 1 (0)
| 2 | INDEX UNIQUE SCAN | SYS_C0016603 | 1 | | 1 (0)
--------------------------------------------------------------------------------

12 rows selected

--可见使用了在字段id上的索引;
select * from z_hash t WHERE t.id=1573;
--这时用到了隐式类型转换，分析结果如下：
SQL> explain plan for select * from z_hash t where t.id=200305;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 | 88 (6)|
| 1 | TABLE ACCESS FULL| Z_HASH | 1 | 104 | 88 (6)|
-----------------------------------------------------------------

11 rows selected
--这时索引失效了

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25261409/viewspace-1044978/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25261409/viewspace-1044978/