探索Oracle自动类型转换_PART1

      类型转换是编程语言中很重要的特性，如主流的C、C++、JAVA、JAVASCRIPT语言，都有类型转换特性，往往在编程语言书籍前几章中就会提及类型转换规则。而我很少看到Oracle相关书籍，对此系统地进行总结。虽然Oracle文档上有所提及，但是因为文档庞大，实例较少，很少引起Oracle技术人员的关注，当查找问题的时候，很难意识到这是类型转换惹的祸。所以，我觉得很有必要写一点关于Oracle类型转换的东西，对文档中的内容进行扩展，结合常见类型转换问题，说明自动类型转换的重要性。显式类型转换是我们自己控制的，这是Oracle技术人员熟悉的部分，因此，本部分的重点是探讨Oracle自动类型转换规则。

       Oracle中对不同类型数据的处理有显式类型转换(Explicit)和自动类型转换(或叫隐式类型转换Implicit)两种方式。这和其他语言类似，对显式类型转换，是可控的，但是对自动类型转换，不建议使用，因为很难控制，有不少缺点，比如可能会对性能产生不好的影响。虽然Oracle不建议使用自动类型转换，但是在Oracle开发中，会经常遇到自动类型转换，这时如果你不了解自动类型转换的规则，那么查找和解决问题就会变得很困难。所以，Oracle开发和维护人员很有必要了解自动类型转换的相关规则，这样，对规范编程和查找问题都有很大帮助

      本章首先会介绍自动类型转换的缺点，然后阐述Oracle自动类型转换的规则，并结合实例分析自动类型转换可能造成的问题。

为什么不建议使用自动类型转换

      自动类型转换的确可以让我们少写一些代码，比如可以少写个TO_CHAR函数，SQL看似简单了，但是它却隐藏着危险：

1.  使用显式类型转换会让SQL可读性更强,但是自动类型转换却没有这个优点。如：

SELECT TO_DATE(SYSDATE,'YYYYMM') FROM DUAL;   

   

看到上面的SQL，也许你会想，我没有看错吧，你写的语句是错的，TO_DATE函数中第1个参数是字符类型才对。你提的这个问题很好，我想你是时候需要了解Oracle自动类型转换规则了。我可以很明确地告诉你，这个语句有时正确，但是有时却是错误的，正确与否依赖于具体的上下文，比如这里SYSDATE是DATE类型，那么Oracle需要将DATE类型转为字符类型，这是自动转换的，也就是说，Oracle要自动调用TO_CHAR(date,fmt,nlsparam)函数，这个fmt要依赖于上下文的NLS_DATE_FORMAT，nlsparam要依赖于NLS_DATE_LANGUAGE的设置，下面看测试结果：

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';

会话已更改。

--其实在SQL*PLUS中DATE类型输出就是按照NLS_DATE_FORMAT和

--NLS_DATE_LANGUAGE参数自动转为字符类型的，这里就是先将SYSDATE转为YYYYMMDD格式，然后再转为DATE类型，

--最后因为在SQL*PLUS中输出的是字符串，所以又转为了YYYYMMDD格式的字符串

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMMDD') FROM DUAL;

TO_DATE(

--------

20100611

--下面的出错了，因为自动转换后SYSDATE变为字符串，格式是YYYYMMDD

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMM') FROM DUAL;

SELECT TO_DATE(SYSDATE,'YYYYMM') FROM DUAL

               *

第 1 行出现错误:

ORA-01830: 日期格式图片在转换整个输入字符串之前结束

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMM';

会话已更改。

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMM') FROM DUAL;

TO_DAT

------

201006

--同样地，这个也出错，因为这里的SYSDATE转为YYYYMM格式字符串

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMMDD') FROM DUAL;

SELECT TO_DATE(SYSDATE,'YYYYMMDD') FROM DUAL

               *

第 1 行出现错误:

ORA-01840: 输入值对于日期格式不够长

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMONDD';

会话已更改。

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMONDD') FROM DUAL;

TO_DATE(SYSDAT

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

20106月 11

DINGJUN123>ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

会话已更改。

--看NLS_DATE_LANGUAGE设置对结果的影响

DINGJUN123>SELECT TO_DATE(SYSDATE,'YYYYMONDD') FROM DUAL;

TO_DATE(SYSD

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

2010JUN11

   

    自动类型转换的确难以理解，如果对自动类型转换的规则理解的不够透彻，那么对上面的例子就会感觉匪夷所思。

2.  自动类型转换往往对性能产生不好的影响，特别是左值的类型被自动转为了右值的类型(当然如果你写value=column那就左右值互换了，这里说的左值是常规写法：column=value)。这种方式很可能使本来应该使用索引的而没有用上索引，也有可能会导致结果出错。如：

表已删除。

DINGJUN123>CREATE TABLE t(name VARCHAR2(10));

表已创建。

DINGJUN123>INSERT INTO t VALUES('1');

已创建 1 行。

DINGJUN123>INSERT INTO t VALUES('abc');

已创建 1 行。

DINGJUN123>COMMIT;

提交完成。

DINGJUN123>CREATE INDEX idx_t ON t (name);

索引已创建。

--案例1:自动类型转换导致出错------------------------------------------

--出错因为NAME转为数值类型失败，'abc'是无法转为NUMBER类型的。

DINGJUN123>SELECT * FROM t

  2        WHERE name = 1;

ERROR:

ORA-01722: 无效数字

未选定行

--正确写法

DINGJUN123>SELECT * FROM t

  2        WHERE name = '1';

NAME

------

1

--案例2:自动类型转换导致本该用索引而没有用---------------------------

--NAME = 1，因为NAME是字符类型，字符与数值比较，则字符自动转为数值类型，见执行计划加粗部分，走全表扫描

--查看执行计划没有真正执行，因此不报错

DINGJUN123>EXPLAIN PLAN FOR

  2        SELECT * FROM t

  3        WHERE name = 1;

已解释。

DINGJUN123>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name |

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

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| T    |

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

Predicate Information (identified by operation id):

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

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

Note

-----

   - rule based optimizer used (consider using cbo)

--案例3:不使用自动类型转换，符合要求---------------------------

--没有自动类型转换，走索引了，这里的测试是在RBO优化器下，我没有收集统计信息，这里只是做一个演示

DINGJUN123>EXPLAIN PLAN FOR

  2        SELECT * FROM t

  3        WHERE name = '1';

已解释。

DINGJUN123>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2296882198

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

| Id  | Operation        | Name  |

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

|   0 | SELECT STATEMENT |       |

|*  1 |  INDEXRANGESCAN| IDX_T |

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

Predicate Information (identified by operation id):

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

   1 - access("NAME"='1')

Note

-----

   - rule based optimizer used (consider using cbo)

案例1，如果这个语句很庞大，找这个错误还真不容易，如果使用显式类型转换，找这个错误就容易多了，一般查找类型转换问题，首先看表的字段类型，然后结合执行计划的FILTER部分查看是否发生自动类型转换。

案例2的自动类型转换使表T建立的索引失效(如果直接运行还会出错，但是使用EXPLAIN PLAN查看计划还是可以的)，无法用上索引，导致性能低下，当然，这个测试例子就无所谓性能不性能了。

案例3不使用类型转换，左值和右值都是字符类型，则该走索引就走索引，符合预期结果。

案例1和2中问题是一个低级错误，如果有良好的编码习惯，这种错误自然就可避免。特别是在写存储过程中，一个程序可能会很大，开发人员经常不注意字段类型，导致SQL测试明明性能很好，但是到PL/SQL中运行，效率却很低，这种问题，首先应该定位测试时的SQL与存储过程中的SQL计划是否一致，找出差异，就可以很容易解决这样的问题。

3.  自动类型转换依赖于发生转换时的上下文，比如例1中的DATE类型自动转为字符类型，一旦上下文改变，很可能原先的程序就不能运行，所以存在自动类型转换的程序的可移植性无法保证。

4.  自动类型转换的算法或规则，以后Oracle可能会改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显式类型转换则不存在这个问题。

5.  自动类型转换是要消耗时间的，当然同等的显式类型转换时间也差不多，最好的方法就是避免类似的转换，对于显式类型转换，最好不要对左值(第2点已经说了左值是相对的)进行类型转换，到时候有索引也用不上，可能还要建函数索引，索引储存和管理开销增大。如：

表已删除。

DINGJUN123>CREATE TABLE t

  2           AS

  3           SELECT SYSDATE+LEVEL done_date

  4           FROM DUAL

  5           CONNECT BY LEVEL < 10;

表已创建。   

DINGJUN123>CREATE INDEX idx_t ON t (done_date);

索引已创建。 

现在有这样的需求：需要查找指定日期的行，我经常看到有人这么写：

DINGJUN123>SET AUTOTRACE ON EXPLAIN

DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

会话已更改。

DINGJUN123>SELECT * FROM t

  2        WHERE TO_CHAR(done_date,'YYYYMMDD') = '20100612';

DONE_DATE

----------

2010-06-12

执行计划

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name |

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

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| T    |

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

Predicate Information (identified by operation id):

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

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("DONE_DATE"),'YYYYMMDD')='201006

              12')

Note

-----

   - rule based optimizer used (consider using cbo)

    这种写法对左值进行了显式类型转换，导致索引失效，是不很好的写法，但是在实际开发中却 是屡见不鲜 特别是对日期类型的处理(看优化器中还冒出了个INTERNAL_FUNCTION，对于这个不必深究，这个内部函数在日期类型的自动类型转换中经常出现，比如DATE类型自动转为TIMESTAMP)，强烈建议改变此坏习惯。改写它：

DINGJUN123>SELECT * FROM t

  2           WHERE done_date >= TO_DATE('20100612','YYYYMMDD')

  3           AND done_date < TO_DATE('20100613','YYYYMMDD');

DONE_DATE

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

2010-06-12

执行计划

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

Plan hash value: 2296882198

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

| Id  | Operation        | Name  |

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

|   0 | SELECT STATEMENT |       |

|*  1 | INDEXRANGESCAN| IDX_T |

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

Predicate Information (identified by operation id):

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

   1 - access("DONE_DATE">=TO_DATE('2010-06-12 00:00:00', 'yyyy-mm-dd

              hh24:mi:ss') AND "DONE_DATE"

              hh24:mi:ss'))

Note

-----

   - rule based optimizer used (consider using cbo)

    OK，索引生效，符合预期，其实很少遇到不能对右值进行改写或不能改写SQL条件而必须要对左值进行转换的情况，如果真遇到这种情况，可以考虑函数索引。