MOS文章实验：ORA-01722 from Queries with Dependent Predicates

今天读了一篇MOS文章，《ORA-01722, ORA-01839, ORA-01841, ORA-01847 or ORA-01858 from Queries with Dependent Predicates (文档 ID 232243.1)》，整篇文章的目的就是为了阐述对于包含相互依赖关系谓词的SQL语句产生错误的可能原因(To explain the possible causes of these errors in SQL statements that include predicates that are dependent on each other)。

文章指出可能的错误类型包括以下几种：  

ORA-01722 invalid numberORA-01790: expression must have same datatype as corresponding expressionORA-01847 day of month must be between 1 and last day of monthORA-01858 a non-numeric character was found where a numeric was expectedORA-01839 date not valid for month specifiedORA-01841 (full) year must be between -4713 and +9999, and not be 0ORA-01843 not a valid month

如果应用程序设计中需要对不同类型的数据做比较，但又没有显示转换，那么Oracle自己会根据一些规则做必要的类型转换。当使用松散类型('loose typing')字段，且包含可变谓词顺序的场景下，在类型转换发生之前如果不能删除会产生错误的列值，那么就有可能产生上述的一些类型转换错误。

除了修改应用程序，能否解决也取决于查询语句。Oracle提供了/*+ ordered_predicates */这个HINT可以作为workaround，但前提是需要以要求的解析顺序来改写查询语句。

还有一种更复杂的场景，就是使用视图。CBO可以创建满足查询条件的最优执行计划。这就意味着通常会将视图和主查询合并，我们也不能控制谓词的解析顺序。文章举了一个示例，如下SQL查询：

   select id   from (select id, data            from data_table            where data_type='housenum'           )   where to_number(data) = 22;

他会变成如下等价的形式：

   select id   from data_table   where data_type='housenum'   and to_number(data) = 22;

如果视图或内联视图使用/*+ no_merge */这个HINT，那么就可以防止视图被重写(合并)。另一个可以阻止因视图合并导致错误的方法就是增加一个'不相关'的rownum谓词(例如rownum > 0)，也会防止视图合并。不能合并的视图就不会允许谓词和主查询的谓词合并使用，也就避免了错误的产生。当然，未来的版本可能会察觉到并删除这样'不相关'的谓词。

9.2.0.7.0SQL> create table data_table          (id     number          ,data_type      varchar(12)          ,data           varchar(30)          );Table created.SQL> insert into data_table values (1234, 'company','Pet Foods Inc');1 row created.SQL> insert into data_table values (1234, 'contact','Jennifer');1 row created.SQL> insert into data_table values (1234, 'zip','22');1 row created.SQL> insert into data_table values (1234, 'shipdate','03-OCT-2003');1 row created.SQL> commit;Commit complete.SQL> select id          from data_table          where data_type='contact'          and  data='Jennifer';        ID----------      1234

正常。数据类型和数据列值都是VARCHAR字符串，不需要类型转换。

实验语句1：

SQL> select id          from data_table          where data_type='zip'          and  to_number(data) = 22;       and  to_number(data) = 22            *ERROR at line 4:ORA-01722: invalid number会提示to_number的处理存在无效数字。对其执行explain plan for，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter(TO_NUMBER("DATA_TABLE"."DATA")=22 AND              "DATA_TABLE"."DATA_TYPE"='zip')

这里需要将VARCHAR类型的字段转换为NUMBER类型，然后和一个数字类型的值进行比较。对于data列中22这个记录，包含NUMBER数字类型，因此转换是有效的，但对于其他行，这种转换就是无效的，因为不包含等价的数值，例如'Pet Foods Inc'。如果谓词比较是对包含非数字类型的行，此时需要非数字类型值和数字类型值进行比较，在做类型转换的时候就会报错。如果谓词比较从'data_type'列开始，删除所有包含非数字类型的行，那么就不会产生错误。

实验语句2：

如下SQL，如果首先解析内联视图，所有data列包含非数字类型值的行都会被过滤。

SQL> select id          from (select id, data          from data_table          where data_type='zip'          )          where to_number(data) = 22;        ID----------      1234这里的子查询仅会选择出data_type是ZIP的行，对应的data列值不包含非数字，因此可以正常执行。对其执行explain plan for，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("DATA_TABLE"."DATA_TYPE"='zip' AND              TO_NUMBER("DATA_TABLE"."DATA")=22)

先解析子查询，用data_type='zip'过滤后，结果集的行data列都是数字型，因此to_number()可以正常执行。

9i下我们看看优化器的模式是RBO：

SQL> show parameter optimizerNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------optimizer_mode                       string                 CHOOSE

如果此时收集表的统计信息，那么会影响执行计划的选择。

SQL> analyze table data_table compute statistics;Table analyzed.

或者将session优化器模式改为CBO：

SQL> alter session set optimizer_mode='ALL_ROWS';Session altered.

得到以下相同的结论。(原因：因为RBO下如果表存在统计信息，则会采用CBO)

实验语句3：SQL> select id          from data_table          where data_type='zip'          and  to_number(data) = 22          ;        ID----------      1234这条语句执行explain plan for，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("DATA_TABLE"."DATA_TYPE"='zip' AND              TO_NUMBER("DATA_TABLE"."DATA")=22)

会先使用data_type='zip'过滤，然后再执行to_number()。

实验语句4：

SQL> select id          from (select id, data                   from data_table                   where data_type='zip'                   )          where to_number(data) = 22;       where to_number(data) = 22             *ERROR at line 6:ORA-01722: invalid number但是现在第二条SQL执行报错。这条语句执行explain plan for，发现谓词条件变为如下(顺序变了)：PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter(TO_NUMBER("DATA_TABLE"."DATA")=22 AND              "DATA_TABLE"."DATA_TYPE"='zip')

CBO下或者收集表的统计信息后，都会先执行to_number()，再使用data_type='zip'，因此报错。

11.2.0.1.0SQL> create table data_table          (id     number          ,data_type      varchar(12)          ,data           varchar(30)          );Table created.SQL> insert into data_table values (1234, 'company','Pet Foods Inc');1 row created.SQL> insert into data_table values (1234, 'contact','Jennifer');1 row created.SQL> insert into data_table values (1234, 'zip','22');1 row created.SQL> insert into data_table values (1234, 'shipdate','03-OCT-2003');1 row created.SQL> commit;Commit complete.优化器模式是CBO：SQL> show parameter optimizerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_mode                       string      ALL_ROWS

实验语句5：

SQL> select id          from data_table          where data_type='zip'          and  to_number(data) = 22;        ID----------      1234执行explain plan for，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("DATA_TYPE"='zip' AND TO_NUMBER("DATA")=22)

实验语句6：

SQL> select id          from (select id, data                   from data_table                   where data_type='zip'                   )         where to_number(data) = 22;        ID----------      1234执行explain plan for，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("DATA_TYPE"='zip' AND TO_NUMBER("DATA")=22)

但当该表收集统计信息后，实验语句6就会报错：

SQL> select id  2            from (select id, data  3            from data_table  4            where data_type='zip'  5            )  6            where to_number(data) = 22;          where to_number(data) = 22                *ERROR at line 6:ORA-01722: invalid number使用explain plan for后，PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter(TO_NUMBER("DATA")=22 AND "DATA_TYPE"='zip')

即使使用/*+ no_merge */也如此。

总结：

1. 9i下，优化器默认模式是RBO，如果表没有统计信息，只会按照RBO方式，SQL查询语句按照谓词从右侧至左侧的顺序解析，如实验语句1。

2. @dbsnake的书中曾介绍过，Oracle会内置一些查询转换规则，只要目标SQL满足了这些规则的要求，Oracle就会对其执行查询转换。Oracle 9i中查询转换是独立于优化器的，和优化器类型无关，因为Oracle此时认为经过查询转换后的等价改写SQL的执行效率一定比原目标SQL的执行效率高。我猜9i内置的查询转换规则，会先对子查询内联视图做解析，或者子查询展开后条件为where to_number(data) = 22 and data_type='zip';，如实验语句2，但仅仅是猜测。

3. 9i下，如果表有统计信息，或者alter session设置session级优化器模式为CBO，如MOS中提到的“The CBO's function is to generate execution plans that satisfy the query in the most optimal way it can.”，实验语句3指出CBO下，对谓词顺序进行了调整，先使用data_type='zip'过滤，然后再执行to_number()。

4. 9i下的CBO，如实验语句4，MOS中提到“That means views often get merged into the main query and so the order in which predicates are evaluated is not under your control”，从现象看先解析to_number()，导致报错。猜测做了子查询展开，谓词条件是where data_type='zip' and to_number(data) = 22，但从10053中，没有看到原因。

5. 11g，优化器默认模式是CBO，会对谓词顺序进行调整，和9i下CBO的效果相同，如实验语句5。

6. 同样，没有收集表统计信息的前提下，实验语句6可正常执行。

7. @dbsnake提过Oracle 10g及其以后的版本中，Oracle会对某些类型的查询转换计算成本，只有当等价改写SQL的成本值小于未经过查询转换的原始SQL的成本值时，Oracle才会对目标SQL执行这些查询转换。收集表统计信息后，实验语句5依旧可以正常执行，会按照先解析data_type='zip'，后解析to_number()的方式进行，不会报错。但实验语句6再次执行后出现错误，发现谓词条件变为先解析to_number()，所以报错，猜测收集统计信息后，实验语句6的执行成本发生了变化，导致前后使用了不同的执行路径，收集统计信息后，实验语句6成本值低的执行路径，因为谓词先解析了to_number()，导致报错。但从10053上未看出端倪。

虽然上述还有一些猜疑，暂时无从考证，但至少从这篇文章中学习了以下内容：

1. 不同版本RBO和CBO的使用，以及对执行SQL的影响。

2. explain plan for观察查询语句谓词顺序的变化。

3. 10053查看执行计划的成本选择。