[i=s] 本帖最後由 wei-xh 於 2013-9-29 10:34 編輯

ORACLE中的隱式資料型別轉換可能有很多你沒留意到的內容。
create table test (id varchar2(100));
create unique index test_id_ind on test(id);

建立了一張表，列id的型別為varchar2，id上有唯一索引。
我們做如下查詢，id的值設定為數字型的1。

SQL> SELECT * FROM test WHERE id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    1 | 52 |    2 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    1 | 52 |    2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

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

執行計劃的尾部出現了TO_NUMBER("ID")=1，然後不可避免的執行計劃走了全表掃描。
可能大多數的人都認為ORACLE會採用如下的資料型別轉換：
WHERE id = 1

轉換為
id=to_char(1)

但是實際發生的轉換卻是：
to_number(id)=1

ORACLE在查詢的列上採用函式，導致索引被忽略，可能這點讓你費解，為什麼ORACLE不在查詢條件上採用函式，而在查詢列上使用，難道ORACLE就不關注效率嗎？

不然。
ORACLE當然關注效率，但是ORACLE更多時候更應該尊重SQL所表達的意圖以及返回結果的正確性。

當你發出一個查詢
select * from test where id=1的時候，ORACLE理解的你的意圖是想查詢出ID列轉換為數值後，結果為1的記錄，如果不這樣做，有可能會導致結果的不正確。
當數字以字元型別被儲存的時候，1可以被儲存為‘1’，也可以被儲存為‘001’，‘0000001’等等，看如下實驗：

SQL> insert into test values('1');

1 row created.

SQL> insert into test values('0001');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test where id=1;

ID
---------------------------------
1
0001

select to_number('0000000000001') from dual;

TO_NUMBER('0000000000001')
--------------------------
                     1
當你發出一個查詢
select * from test where id=1
如果ORACLE自以為是的把你的查詢條件變為id=to_char(1)，那麼返回的結果將會不正確：
SQL> select * from test where id=to_char(1);

ID
-----------------------------------
1
丟失了id為0001的記錄

但是你可能會說，不對，這不符合我的預期，我希望返回的只是id為'1'的記錄，既然這樣，那還是請你遵守ORACLE的法則，明確的寫明：id=to_char(1)或者id='1'

還有一點需要注意，隱式轉換過程中，可能出現轉換錯誤：
SQL> select * from test;

ID
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1
0001

insert into test values('xxx');
commit;

SQL> select * from test where id=1;
ERROR:
ORA-01722: invalid number

由於xxx不能被轉換為數字，因此在查詢裡的隱式型別轉換錯誤，導致查詢報錯。

這裡說了隱式轉換的幾個問題，為了規避這些問題，我們應該遵守：
1）如果一個列為數值型的，那就將列設定為數值型，不要設定為varchar2
2）寫查詢條件的時候，請注意列的字元型別，避免型別的隱式轉換，除非這個轉換是你需要的，否則都請顯式的進行轉換。

ORACLE中的隱式資料型別轉換（一）

相關文章