Oracle 隱式轉換

DBA_建瑾發表於2014-02-27
Oracle

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

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29324876/viewspace-1096741/，如需轉載，請註明出處，否則將追究法律責任。

相關文章