在文章《【問題處理】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 --

【隱式轉換】注意隱式轉換將導致索引無法使用

相關文章