首页 > 代码库 > 【转载】关于oracle隐式转换以及转换时的优先级问题

【转载】关于oracle隐式转换以及转换时的优先级问题

以下转载自:http://blog.itpub.net/29324876/viewspace-1096741/
 
 
Oracle中对不同类型的处理具有显式类型转换(Explicit)和隐式类型转换(Implicit)两种方式,对于显式类型转换,我们是可控的,但是对于隐式类型转换,当然不建议使用,

因为很难控制,有不少缺点,但是我们很难避免碰到隐式类型转换,如果不了解隐式类型转换的规则,那么往往会改变我们SQL的执行计划,从而可能导致效率降低或其它问题。

 

1.1  隐式转换发生场景

1.对于INSERTUPDATE操作,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<sysdate;

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")<SYSDATE@!)

注:select * from table_date where varchar_date<sysdate相当于

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

 

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

 

 

 

 

【转载】关于oracle隐式转换以及转换时的优先级问题