怪现象

我们比如匹配某个日期字段的月份的时候如果不简历日期的函数索引，那么是无法走索引的，但是如果我们设置nls_date_format环境变量，那么就走日期字段的索引了。

但是出现如下现象，为什么like可以匹配到值，而等于号却是无法匹配呢？

 

测试如下：

C:\Documents and Settings\Administrator>sqlplus xta7/xta7

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 2月 2 22:55:40 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> desc tab_obj
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(last_ddl_time) from tab_obj where last_ddl_time = '11';
select count(last_ddl_time) from tab_obj where last_ddl_time = '11'
                                                               *
ERROR 位于第 1 行:
ORA-01840: ????????????

SQL> alter session set nls_date_format='MM';

会话已更改。

SQL> select count(last_ddl_time) from tab_obj where last_ddl_time = '11';

COUNT(LAST_DDL_TIME)
--------------------
                   0

SQL> set autotrace on
SQL> select count(last_ddl_time) from tab_obj where last_ddl_time = '11';

COUNT(LAST_DDL_TIME)
--------------------
                   0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IDX_TAB_OBJ' (NON-UNIQUE) (Cost=1
           Card=481 Bytes=3848)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        503  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 count(last_ddl_time) from tab_obj where last_ddl_time = '01';

COUNT(LAST_DDL_TIME)
--------------------
                   0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IDX_TAB_OBJ' (NON-UNIQUE) (Cost=1
           Card=481 Bytes=3848)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        503  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 count(last_ddl_time) from tab_obj where last_ddl_time like '01';

COUNT(LAST_DDL_TIME)
--------------------
              185088

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=218 Card=1 Bytes=8)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TAB_OBJ' (Cost=218 Card=481 Byte
          s=3848)

 

 

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