#每日一记#ORA-01841错误，分析处理过程

最近，遇到了一个关于ORA-01841的报错，起初，认为这个错误处理起来应该不困难，但实际上折腾了很久，才最终找到问题原因，并解决掉，下面将本次解决和分析的过程记录下。ORA-01841的错误提示是“(full) year must be between -4713 and +9999, and not be 0”，翻译过来，大意是完整的年份值需在-4712到+9999之间，并且不得为0。出现这个错误，通常都是数据本身存在问题导致的，但本案例中，又不仅仅是数据的问题。下面就来回顾一下问题处理的过程。为了简化问题，方便理解，以下描述均是在事后构建的模拟环境中进行的：

结合SQL和报错信息，最初的怀疑是内层查询的结果集的C1列上，有不正常的数据，导致出现

我们可以看到，内层查询的结果集中，并没有不正常的数据。到此时，想了许久，也做了各种测试，但均没有找到问题原因。决定看一下执行计划：

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

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

我们来验证一下：

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

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

知道了原因，那我们要如何处理呢？

我们可以改写SQL，使其必须先执行内层查询，然后再执行外层查询。

比如可以在内层查询中加入ROWNUM。

种处理方法，虽然外层的过滤条件被推入到了内层，但会放到后边执行，这样，当前边的条件已经将不正常的数据过滤掉后，也就不会报错了。

同理，对C1做一些UPPER，LOWER的函数运算，也有同样的效果。

但是，这又引起了我的一个新的疑问，如果初始SQL就是只有一层（如下所示），两个过滤条件在一起时，CBO是先用哪个过滤条件来过滤呢？

执行后的结果如下：

如上所示，我们发现仍然会报ora-01841的错误。

和过滤条件在WHERE子句中出现的顺序是否有关呢？试试调换条件后的结果;

如上所示，看来和条件出现的顺序是无关的。

但是，如果是RBO（基于规则的优化器）模式，则会是先使用最后出现的条件，再使用前边的。即，从后往前施加条件。这也是为什么网上曾流传过的一个SQL编写技巧–将过滤性最好的条件写到WHERE子句中的最后。但，自Oracle 10g以后，默认就是CBO（基于成本的优化器）了，除非像上面实验那样使用RULE的提示，否则，都会是以CBO方式来运作。

这正好给了我们一个启示，在CBO下，在选择先执行哪个过滤条件时，是否会依据统计信息，计算并排序各个过滤条件的选择性，选择性越好的，则越会先被执行呢？

我们测试验证一下。主要测试思路如下：

1、默认情况下，CBO估算大部分非相等的过滤条件时，都会采用5%这样一个选择率。所以，条件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的选择率会是5%，即，经过该条件过滤后，CBO认为会返回总记录的5%的行数。

2、CBO在计算NOT LIKE这类条件时，其计算思路是先计算出LIKE的选择率（类似于相等条件，是条件列中唯一值数量的倒数），然后用1-（like的选择率）就是NOT LIKE的选择率。

3、向表中再插入94行形如‘XXXXXXXXX1’这样的记录。构造一个有100行记录的表，其中c1列上有100个唯一值，然后收集统计信息（注意，不要收集列上的直方图信息，因为在有直方图时，其计算逻辑和方法都要复杂得多，这里，我们只用列上的非直方图的统计信息）。操作过程如下：

分别来验证一下施加单个条件时，CBO的估算结果

看看是否与前边的理解是吻合的：

如上所示，对条件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”返回行数的估算是5行。由于表中总共有100行，所以，选择率是5/100=5%。与我们的理解是吻合的。

学习学习，感谢分享

坚持每天登录论坛学习

1111111111111

支持一下。顺便盖楼。。。

感谢楼主分享，这个很实用。

感谢楼主分享，这个很实用。

感谢楼主分享，这个很实用。

感谢楼主分享，这个很实用。

感谢楼主分享，这个很实用。