今天读了一篇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, datafrom data_tablewhere 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(idnumber,data_typevarchar(12),datavarchar(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 idfrom data_tablewhere data_type=’contact’and data=’Jennifer’;ID———-1234
正常。数据类型和数据列值都是VARCHAR字符串，不需要类型转换。
实验语句1：
SQL> select idfrom data_tablewhere 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 idfrom (select id, datafrom data_tablewhere 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’ ANDTO_NUMBER("DATA_TABLE"."DATA")=22)先解析子查询，用data_type=’zip’过滤后，结果集的行data列都是数字型，因此to_number()可以正常执行。
9i下我们看看优化器的模式是RBO：
SQL> show parameter optimizerNAMETYPEVALUE———————————— ———————- ——————————optimizer_modestringCHOOSE
如果此时收集表的统计信息，那么会影响执行计划的选择。
SQL> analyze table data_table compute statistics;Table analyzed.
或者将session优化器模式改为CBO：SQL> alter session set optimizer_mode=’ALL_ROWS’;Session altered.
得到以下相同的结论。(原因：因为RBO下如果表存在统计信息，则会采用CBO)
为了一些琐事吵架，然后冷战，疯狂思念对方，最后和好。
