今天读了一篇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)。
文章指出可能的错误类型包括以下几种：
实验语句6：
ORA-01722 invalid number
ORA-01790: expression must have same datatype as corresponding expression
ORA-01847 day of month must be between 1 and last day of month
ORA-01858 a non-numeric character was found where a numeric was expected
ORA-01839 date not valid for month specified
ORA-01841 (full) year must be between -4713 and +9999, and not be 0
ORA-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.0
SQL> 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 optimizer
NAME 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.0
SQL> 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 optimizer
NAME 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查看执行计划的成本选择。
