Oracle 索引导致ORA-01843错误

今天我查询一个复合索引表，但遇到了一个错误。问题如下：
我有一张像这样的表
创建表CERT_INFO
(
CERT_TYPE VARCHAR2(19) ，
CERT_CODE VARCHAR2(40) ，
忽略_ CUST VARCHAR2(1)
)
表有超过100万条记录，有些记录像这样

cert_type       cert_code          ignore_cust
101  130984198308100385 
101  131022198508094218 
101  131126198111194828            1
101  132324197511260219 
101  132335198002083094 

我还为表创建了索引：
create index idx_cert_1 on cert_info(cert_type, cert_code);
使用此查询时，出现ORA-01843错误：
select *
from (select b.*, to_date(substr(cert_code, 7, 8), 'yyyymmdd') birthday
from cert_info b
where cert_type = '101'
and ignore_cust is null
and length(cert_code) = 18)
where birthday > sysdate

然后我把索引丢了， sql工作！为什么? oracle文档提醒我索引不影响查询时间不影响查询结果... thx !

您可以在一些行中看到substr(cert_code, 7, 8)不是一个真正的日期。

如果没有索引， Oracle将完全扫描表，“当它们出现时”读取这些行。如果在SQL开发或其他客户机中运行查询，但在读取N行后停止处理，则您只是还没有到达达夫数据。

该索引包含证书代码。条目按证书类型排序，然后按证书代码排序。因此，如果有一个在Oracle找到N之前不是真正的日期的证书代码，它就会失败。

例如，我插入了一串在将来（ 2080年）的“日期”代码，然后我插入了一个无效的“日期”1980-22-08。这是在表中的有效行之后。

我只从表中读取一行（其中， rownum = 1 ）。全表扫描按照我插入的顺序读取数据。一旦找到与where子句匹配的行，此操作将立即停止：

SQL> create table cert_info (
  2    cert_type varchar2 ( 19 ) , cert_code varchar2 ( 40 ) , ignore_cust varchar2 ( 1 )
  3  ) ;

Table created.

Elapsed: 00:00:00.11
SQL> insert into cert_info
  2  select 101,132335208002083094, null from dual connect by level <= 1000;

1000 rows created.

Elapsed: 00:00:00.12
SQL>
SQL> insert into cert_info values (101,132335198022083094, null);

1 row created.

Elapsed: 00:00:00.11
SQL> commit;

Commit complete.

Elapsed: 00:00:00.13
SQL>
SQL> select *
  2  from
  3    (select b.*, to_date ( substr ( cert_code, 7, 8 ) , 'yyyymmdd' ) birthday
  4    from cert_info b
  5    where cert_type          = '101'
  6    and ignore_cust         is null
  7    and length ( cert_code ) = 18
  8    )
  9  where birthday > sysdate
 10  and   rownum = 1;

CERT_TYPE           CERT_CODE                                I BIRTHDAY
------------------- ---------------------------------------- - ---------
101                 132335208002083094                         08-FEB-80

它尚未读取无效的日期（尚未）。请删除rownum子句，我们将得到错误（最终） ：

SQL> select *
  2  from
  3    (select b.*, to_date ( substr ( cert_code, 7, 8 ) , 'yyyymmdd' ) birthday
  4    from cert_info b
  5    where cert_type          = '101'
  6    and ignore_cust         is null
  7    and length ( cert_code ) = 18
  8    )
  9  where birthday > sysdate;

CERT_TYPE           CERT_CODE                                I BIRTHDAY
------------------- ---------------------------------------- - ---------
101                 132335208002083094                         08-FEB-80
101                 132335208002083094                         08-FEB-80
...  ...
101                 132335208002083094                         08-FEB-80
101                 132335208002083094                         08-FEB-80
ERROR:
ORA-01843: not a valid month

有了索引， Oracle必须按顺序读取数据。1980年是2080年以前。因此，它检查无效的行，以查看它是否比其他行先于sysdate。这不是一个真正的约会，所以这抛出了一个例外：

SQL> create index idx_cert_1 on cert_info
  2    ( cert_type, cert_code
  3    ) ;

Index created.

Elapsed: 00:00:00.20
SQL>
SQL> select *
  2  from
  3    (select b.*, to_date ( substr ( cert_code, 7, 8 ) , 'yyyymmdd' ) birthday
  4    from cert_info b
  5    where cert_type          = '101'
  6    and ignore_cust         is null
  7    and length ( cert_code ) = 18
  8    )
  9  where birthday > sysdate
 10  and   rownum = 1;
  (select b.*, to_date ( substr ( cert_code, 7, 8 ) , 'yyyymmdd' ) birthday
                         *
ERROR at line 3:
ORA-01843: not a valid month

你平时不打这个只是运气。非索引查询可能随时命中此查询。

为确保避免这种情况，您需要修复数据。理想情况下，将日期存储在实际日期列中。