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@]