1 Oracle 隱式轉換

Oracle中對不同型別的處理具有顯式型別轉換(Explicit)和隱式型別轉換(Implicit)兩種方式，對於顯式型別轉換，我們是可控的，但是對於隱式型別轉換，當然不建議使用,
因為很難控制，有不少缺點，但是我們很難避免碰到隱式型別轉換,如果不瞭解隱式型別轉換的規則，那麼往往會改變我們SQL的執行計劃，從而可能導致效率降低或其它問題。

1.1 隱式轉換髮生場景

1.對於INSERT和UPDATE操作，oracle會把插入值或者更新值隱式轉換為欄位的資料型別。

例如：

SQL> create table text(id varchar2(32),name varchar2(10),age number);

Table created.

SQL> insert into text values ('1','Jack','18');

1 row created.

SQL> update text set age='19';

1 rows updated.

SQL> select * from text;

ID NAME AGE

---------- ---------- ----------

1 Jack 19

注：insert into text values ('1','Jack','18') 相當於 insert into text values(,’1’,’Jack’,to_number('18'))

update text set age='19'相當於update text set age=to_number('19')

2.當比較字元型和數值型的值時，oracle會把字元型的值隱式轉換為數值型。例如：

SQL> explain plan for select * from text where age='19';

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 738342525

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 66 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEXT | 2 | 66 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("AGE"=19)

注：select * from text where age='19'相當於select * from text where age=to_number('19')

SQL> explain plan for select * from text where id=1;

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 738342525

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEXT | 1 | 38 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

注：select * from text where id=1;相當於select * from text where to_number(id)=1

如果id列建有索引此時將失效

3.當比較字元型和日期型的資料時，oracle會把字元型轉換為日期型。例如：

SQL> create table table_date(varchar_date varchar2(20),date_date Date);

Table created.

SQL> insert into table_date values(to_char(sysdate,'yyyy-mm-dd'),sysdate);

1 row created.

SQL> select * from table_date;

VARCHAR_DATE DATE_DATE

-------------------- ---------

2014-02-26 26-FEB-14

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> explain plan for select * from table_date where varchar_date

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 1510990824

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TABLE_DATE | 1 | 21 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(INTERNAL_FUNCTION("VARCHAR_DATE")

注：select * from table_date where varchar_date相當於

select * from table_date where to_date(varchar_date,’yyyy-mm-dd hh24:mi:ss’)

SQL> explain plan for select * from table_date where date_date>'2014-2-26 0:0:0';

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 1510990824

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TABLE_DATE | 1 | 21 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DATE_DATE">TO_DATE(' 2014-02-26 00:00:00', 'syyyy-mm-dd

hh24:mi:ss'))

注：select * from table_date where date_date>'2014-2-26 0:0:0'相當於

select * from table_date where date_date>to_date('2014-2-26 0:0:0’, ’yyyy-mm-dd hh24:mi:ss’

4. 隱式轉換髮正在欄位列上時將使索引失效。例如：

1）當末發生隱式轉換時索引有效

SQL> create table t1 as select OBJECT_ID as id ,to_char(OBJECT_ID) as vid from dba_objects;

Table created.

SQL> desc t1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

VID VARCHAR2(40)

SQL> create index t1_ind_vid on t1(vid);

Index created.

SQL> explain plan for select * from t1 where vid='15612';

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 1215445203

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T1_IND_VID | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("VID"='15612')

注：未發生隱式轉換正常執行索引掃

2）當欄位列發生隱式轉換時索引將失效

SQL> explain plan for select * from t1 where vid=15612;

Explained.

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

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 11 | 48 (5)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 11 | 48 (5)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(TO_NUMBER("VID")=15612)

注：select * from t1 where vid=15612 相當於select * from t1 where to_number(vid)=15612,vid列發生隱式轉換執行計劃顯示全表掃描末使用索引。

1.2 隱式轉換的缺點

1. 使用顯示型別轉換會讓我們的SQL更加容易被理解,也就是可讀性更強,但是隱式型別轉換卻沒有這個優點。

2. 隱式型別轉換往往對效能產生不好的影響，特別是左值的型別被隱式轉為了右值的型別。這種方式很可能使我們本來可以使用索引的而沒有用上索引,也有可能會導致結
果出錯。

3. 隱式型別轉換可能依賴於發生轉換時的上下文環境，比如1中的to_date(sysdate,fmt),一旦上下文環境改變，很可能我們的程式就不能執行。

4. 隱式型別轉換的演算法或規則，以後Oracle可能改變，這是很危險的，意味著舊的程式碼很可能在新的Oracle版本中執行出現問題(效能、錯誤等)，顯示型別轉換總是有最高
的優先順序，所以顯示型別轉換沒有這種版本更替可能帶來的問題。

5. 隱式型別轉換是要消耗時間的,當然同等的顯式型別轉換時間也差不多,最好的方法就是避免類似的轉換,在顯示型別轉換上我們會看到，最好不要將左值進行型別轉換，到
時候有索引也用不上索引，還要建函式索引，索引儲存和管理開銷增大。

小結

Oracle使用資料型別的優先順序來決定隱式型別轉換，原則是將優先順序低的轉換為優先順序高的（資料型別優先順序為：Number>字元型別>日期型別）。隱式轉換髮生在字
段列上時將使索引失效。

DBA_建瑾
2014.2.27

Oracle 隱式轉換

1 Oracle 隱式轉換

1.1 隱式轉換髮生場景

1.2 隱式轉換的缺點

小結

相關文章