剑破冰山笔记之第六章 自动类型转换

DATE格式转换问题

SQL> SELECT TO_DATE(SYSDATE,'yyyymm')From dual;
 
SELECT TO_DATE(SYSDATE,'yyyymm')From dual
 
ORA-01843: 无效的月份
此时sysdate 自动转换为to_char类型
 
SQL> alter session set nls_date_format='yyyymm';
 
Session altered
 
SQL> SELECT TO_DATE(SYSDATE,'yyyymm') from dual;
 
TO_DATE(SYSDATE,'YYYYMM')
-------------------------
2012/6/1

不走索引的例子

SQL> select  r  from tt where to_char(r,'yyyy-mm-dd hh24:mi:ss')='2039-07-25 17:
25:10';

R
-------------------
2039-07-25 17:25:10


执行计划
----------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     8  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TT   |     1 |     9 |     8  (13)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("R"),'yyyy-mm-dd
              hh24:mi:ss')='2039-07-25 17:25:10')

Note
-----
   - dynamic sampling used for this statement

走索引的例子

create table tt as select sysdate+level r from dual connect by level<10000;
create index tt_r on tt(r);

 select /*+ index(r)  */  r  from tt where r='2039-07-25 17:25:10';

R
-------------------
2039-07-25 17:25:10


执行计划
----------------------------------------------------------
Plan hash value: 2332884906

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     9 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TT_R |     1 |     9 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("R"=TO_DATE('2039-07-25 17:25:10', 'yyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

类型转换

自动类型转化的规则和算法不定，版本不一样，规则可能不一样
自动类型转化消耗资源

隐式转换

binary float自动转换为binary double 准确，反之，不行

select ‘10’+0 from dual；

select ‘10’||0 from dual；

select ‘ab’+1 from dual；

字符转换的优先级

字符转换的优先级，优先级越高，越被容易转成

nvarchr2>nchar>varchar2>char

按省份的时候地区排序问题
一字多音的存在比方说‘重庆’ CHONG和ZHONG 首字母二进制排序