原文地址:http://blog.itpub.net/29324876/viewspace-1096741/
1Oracle隐式转换
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 Jack19
注:insert into text values ('1','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;
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEXT | 1 | 38 | 2 (0)| 00:00:01 |
1 - filter(TO_NUMBER("ID")=1)
注:select * from text where id=1;相当于select * from text whereto_number(id)=1
如果id列建有索引此时将失效
3.当比较字符型和日期型的数据时,oracle会把字符型转换为日期型。例如:
SQL> create table table_date(varchar_date varchar2(20),date_date Date);
SQL> insert into table_date values(to_char(sysdate,'yyyy-mm-dd'),sysdate);
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 wherevarchar_date<sysdate;< span="" style="word-wrap: break-word;">
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 |
1 - filter(INTERNAL_FUNCTION("VARCHAR_DATE")<sysdate@!)< span="" style="word-wrap: break-word;">
注:select * from table_date wherevarchar_date<sysdate< span="" style="word-wrap: break-word;">相当于
select * from table_date whereto_date(varchar_date,’yyyy-mm-dd hh24:mi:ss’)<sysdate< span="" style="word-wrap: break-word;">
SQL> explain plan for select * from table_date where date_date>'2014-2-26 0:0:0';
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;
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
VIDVARCHAR2(40)
SQL> create indext1_ind_vidon t1(vid);
Index created.
SQL> explain plan for select * from t1 where vid='15612';
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 |
2 - access("VID"='15612')
注:未发生隐式转换正常执行索引扫
2)当字段列发生隐式转换时索引将失效
SQL> explain plan for select * from t1 where vid=15612;
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 |
1 - filter(TO_NUMBER("VID")=15612)
注:select * from t1 where vid=15612相当于select * from t1 whereto_number(vid)=15612,vid列发生隐式转换执行计划显示全表扫描末使用索引。
1.2隐式转换的缺点
1.使用显示类型转换会让我们的SQL更加容易被理解,也就是可读性更强,但是隐式类型转换却没有这个优点。
2.隐式类型转换往往对性能产生不好的影响,特别是左值的类型被隐式转为了右值的类型。这种方式很可能使我们本来可以使用索引的而没有用上索引,也有可能会导致结 果出错。
3.隐式类型转换可能依赖于发生转换时的上下文环境,比如1中的to_date(sysdate,fmt),一旦上下文环境改变,很可能我们的程序就不能运行。
4.隐式类型转换的算法或规则,以后Oracle可能改变,这是很危险的,意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等),显示类型转换总是有最高 的优先级,所以显示类型转换没有这种版本更替可能带来的问题。
5.隐式类型转换是要消耗时间的,当然同等的显式类型转换时间也差不多,最好的方法就是避免类似的转换,在显示类型转换上我们会看到,最好不要将左值进行类型转换,到 时候有索引也用不上索引,还要建函数索引,索引储存和管理开销增大。
原文地址:http://www.cnblogs.com/iliuyuet/p/4104469.html
oracle有三种最基本的数据类型,即字符型、数值型、日期型。 oracle提供的单行函数中,针对不同的数据类型,提供大量实用的函数,同时提供一系列数据类型转换函数,如下: 1)to_char 数值、日期->字符型 语法:to_char(num|date,[formatmask],[nls_parameters]) 参数:num|date待转换的数值或者日期 formatmask:可选参数
数字->字符型的可用格式
|