隐式转换的问题

今天监控一条SQL语句的时候，发现一条SQL语句的日期格式不对也能查询：

select * from xxx where date1<='2010/09/21' and date2>='2010/09/21'

date1和date2也是日期格式，NLS_DATE_FORMAT设置的是'YYYY-MM-DD HH24:MI:SS'

看到这条SQL语句的时候感觉奇怪，格式不对也能执行。后来经过一番测试，发现很多格式都是可以用的。

SQL> create table  t(date_col date);

Table created.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> insert into t values('2010-09-21');

1 row created.

SQL> select * from t;

DATE_COL
-------------------
2010-09-21 00:00:00

SQL> insert into t values('2010/09/22');

1 row created.

SQL> select * from t;

DATE_COL
-------------------
2010-09-21 00:00:00
2010-09-22 00:00:00

SQL> insert into t values('20100923');

1 row created.

SQL> select * from t;

DATE_COL
-------------------
2010-09-21 00:00:00
2010-09-22 00:00:00
2010-09-23 00:00:00

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> insert into t values('2010-09-21');

1 row created.

SQL> insert into t values('2010/09/22');

1 row created.

SQL> insert into t values('20100923');

1 row created.

SQL> insert into t values('2010\09\24');

1 row created.

SQL> select * from t;

DATE_COL
-------------------
2010/09/21 00:00:00
2010/09/22 00:00:00
2010/09/23 00:00:00
2010/09/24 00:00:00

SQL> rollback;

Rollback complete.

貌似ALTER SESSION SET NLS_DATE_FORMAT不起作用了

甚至使用$分割也可以：

SQL> insert into t values('2010$09$25');

1 row created.

貌似只要日期年月日 之间的间隔不是数字或者字符A-z都可以。

SQL> insert into t values('2010+12+13');

1 row created.

SQL> insert into t values('2010 12 13');

1 row created.

SQL> insert into t values('201011213');
insert into t values('201011213')
                     *
ERROR at line 1:
ORA-01861: literal does not match format string

SQL> insert into t values('2010T12T13');
insert into t values('2010T12T13')
                     *
ERROR at line 1:
ORA-01861: literal does not match format string

SQL> select * from t;

DATE_COL
-------------------
2010/09/25 00:00:00
2010/12/13 00:00:00
2010/12/13 00:00:00

下面的几种方式查询都能查询出来

SQL> select * from t where date_col='2010/09/25';

DATE_COL
-------------------
2010-09-25 00:00:00

SQL> select * from t where date_col='2010-09-25';

DATE_COL
-------------------
2010-09-25 00:00:00

SQL> select * from t where date_col='20100925';

DATE_COL
-------------------
2010-09-25 00:00:00

SQL> select * from t where date_col='2010$09$25';

DATE_COL
-------------------
2010-09-25 00:00:00

SQL> select * from t where date_col='2010+09+25';

DATE_COL
-------------------
2010-09-25 00:00:00

既然这些格式都能用，也能得到正确的结果。那么date_col列就不能进行隐式转换了。因为ORACLE不知道查询的 字符串的格式(TO_CHAR(date_col,'XXXX'),XXXX这块
应该没法填写吧)。ORACLE应该在字符串上进行了格式化为日期。 不过执行计划中并没有给出详细的信息。

SQL> select * from t where date_col='2010$09$25';

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |    11 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     9 |    11 |
----------------------------------------------------------

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

   1 - filter("DATE_COL"='2010$09$25')

既然DATE_COL列不用进行隐式转换了，那么DATE_COL列上有索引的时候就可以用到索引了。

SQL> insert into t select trunc(sysdate)-rownum from all_objects;

40794 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t_01 on t(date_col);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot on              
SQL> select * from t where date_col='2010$08$22';

DATE_COL
-------------------
2010-08-22 00:00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 992320858

-------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     8 |     1 |
|*  1 |  INDEX RANGE SCAN| IDX_T_01 |     1 |     8 |     1 |
-------------------------------------------------------------

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

   1 - access("DATE_COL"='2010$08$22')

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

以前一直认为隐式转换不会走索引了，看了错了。

 看来ORACLE更加智能化了  以前我记得隐式转换会加在DATA_COL列上

Execution Plan
----------------------------------------------------------
Plan hash value: 992320858

-------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     8 |     1 |
|*  1 |  INDEX RANGE SCAN| IDX_T_01 |     1 |     8 |     1 |
-------------------------------------------------------------

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

 1 - access("DATE_COL"='2010$08$22') 
 
 以前的话应该是1 - access(TO_CHAR("DATE_COL")='2010$08$22')

现在我感觉是加在了右边了 1 - access("DATE_COL"=TO_DATE('2010$08$22','YYYY$MM$DD'))  
不过执行计划中没有给出  如果能给出就更清晰了