奇奇怪怪的ORA-01841错误，分析处理过程（全）

最新推荐文章于 2024-09-03 15:00:00 发布

数据和云

最新推荐文章于 2024-09-03 15:00:00 发布

阅读量1.4w

点赞数

文章标签：人工智能 mysql oracle sql 数据分析

本文链接：https://blog.csdn.net/enmotech/article/details/112342760

版权

本文详细介绍了在遇到ORA-01841错误时，通过分析执行计划和数据，发现是由于CBO对SQL的自动改写导致的。解决方案包括改写SQL，确保先过滤异常数据。同时，探讨了CBO在选择过滤条件的顺序时可能依据的选择性，通过实验验证了选择性在条件执行顺序中的作用。

摘要由CSDN通过智能技术生成

墨墨导读：由于执行计划中，对过滤谓词顺序的改变，导致SQL运行报错。

最近，遇到了一个关于ORA-01841的报错，起初，认为这个错误处理起来应该不困难，但实际上折腾了很久，才最终找到问题原因，并解决掉，下面将本次解决和分析的过程用样例来说明。

ORA-01841的错误提示是“(full) year must be between -4713 and +9999, and not be 0”，翻译过来，大意是完整的年份值需在-4712到+9999之间，并且不得为0。出现这个错误，通常都是数据本身存在问题导致的，但本案例中，又不仅仅是数据的问题。

下面就来回顾一下问题处理的过程。为了简化问题，方便理解，以下描述均是在事后构建的模拟环境中进行的：

执行以下SQL时，发生了ora-01841的报错：

SQL>  select *      from (          select *           from test_tab1          where c1 not like 'X%'          )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'  ;ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected
结合SQL和报错信息，最初的怀疑是内层查询的结果集的C1列上，有不正常的数据，导致出现了报错。因此，首先检查内层查询的结果：

SQL> select *           from test_tab1          where c1 not like 'X%'  ;
  ID C1---------- --------------------------------   1 2020-10-04   2 2020-09-17   3 2020-10-14   4 2020-11-03   5 2020-12-04

我们可以看到，内层查询的结果集中，并没有不正常的数据。

到此时，想了许久，也做了各种测试，但均没有找到问题原因。决定看一下执行计划：

SQL> set autot onSQL>  select *      from (          select *           from test_tab1          where c1 not like 'X%'          )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'  ;ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0


no rows selected

Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id  | Operation    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |        |     1 |    14 |     3  (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TEST_TAB1 |     1 |    14 |     3  (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter(TO_DATE("TEST_TAB1"."C1",'yyyy-mm-dd')>TO_DATE('        2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1" NOT LIKE'X%')


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

从执行计划中看，CBO对该SQL做了自动改写，将外层查询的条件，推到了内层查询。而且，从谓词信息部分，我们可以看到SQL中的条件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”在两个过滤条件中，是位于靠前的位置。

也就是说，当数据库对表中的数据做过滤时，是先用“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”来检查。这样，如果有某行数据的C1列中的值不正常，就会导致这样的报错。

我们来验证一下：

SQL> select * from test_tab1;
  ID C1---------- --------------------------------   1 2020-10-04   2 2020-09-17   3 2020-10-14   4 2020-11-03   5 2020-12-04   6 XXXXXXXXX1
6 rows selected.

果然，最后一行的C1列中的值是不能正常转换为日期的。

未被CBO自动改写的原始SQL，其内层查询，会将不能正常转换为日期的数据排除掉，然后在外层再去做TO_DATE的转换。如果CBO按照这种方式来处理，就不会报错了。

知道了原因，那我们要如何处理呢？
我们可以改写SQL，使其必须先执行内层查询，然后再执行外层查询。
比如可以在内层查询中加入ROWNUM。

SQL> select  *      from (          select  t.*,                  rownum rn           from test_tab1 t          where c1 not like 'X%'          )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01';   2    3    4    5    6    7    8  
        ID C1                                       RN---------- -------------------------------- ----------         4 2020-11-03                                4         5 2020-12-04                                5

Execution Plan----------------------------------------------------------Plan hash value: 4134971776
---------------------------------------------------------------------------------| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |           |     5 |   220 |     3   (0)| 00:00:01 ||*  1 |  VIEW               |           |     5 |   220 |     3   (0)| 00:00:01 ||   2 |   COUNT             |           |       |       |            |          ||*  3 |    TABLE ACCESS FULL| TEST_TAB1 |     5 |    70 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00',              'syyyy-mm-dd hh24:mi:ss'))   3 - filter("C1" NOT LIKE 'X%')

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          8  consistent gets          0  physical reads          0  redo size        711  bytes sent via SQL*Net to client