MOS article experiment: ORA-01722 from Queries with Dependent predicates

I read a MOS article today, ORA-01722, ORA-01839, ORA-01841, ORA-01847 or ORA-01858 from Queries with Dependent predicates (document ID 232 243.1), the purpose of the entire article is to illustrate the possible causes of errors in SQL statements that contain interdependencies predicates (to explain the possible causes of these errors in SQL statements that Inc Lude predicates that is dependent on all other).
the article states that possible types of errors include the following:

ORA-01722 invalid numberora-01790:expression must has same datatype as corresponding expressionORA-01847 day of month Mu St is between 1 and last day of monthORA-01858 a non-numeric character is found where a numeric was expectedORA-01839 dat E not valid for month specifiedORA-01841 (full) Year must is between-4713 and +9999, and not being 0ora-01843 not a valid MO Nth

If you need to compare different types of data in your application design, but do not show transformations, then Oracle will make the necessary type conversions based on some rules. When using a loosely typed (' Loose typing ') field with a variable predicate order, it is possible to generate some of these type conversion errors before the type conversion occurs if the column value that produces the error cannot be deleted.
In addition to modifying the application, it depends on the query statement. Oracle provides/*+ ordered_predicates */This hint can be used as workaround, but only if the query is rewritten in the required parsing order.
There is also a more complex scenario, which is the use of views. The CBO can create an optimal execution plan that satisfies the query criteria. This means that the view is usually merged with the main query, and we cannot control the parsing order of the predicates. This article provides an example of SQL query as follows:

   Select ID from   (select ID, data from            data_table            where data_type= ' HouseNum '           )   where To_number (data ) = 22;

He will become the equivalent of the following form:

   Select ID from   data_table   where data_type= ' HouseNum ' and   to_number (data) = 22;

if the view or inline view uses/*+ No_merge */This hint, then the view can be prevented from being rewritten (merged). Another way to prevent a view merge from causing an error is to add an ' unrelated ' rownum predicate (such as rownum > 0), which also prevents the view from merging. Views that cannot be merged do not allow the verb to be combined with the predicate of the main query, and the error is avoided. Of course, future versions may perceive and delete such ' unrelated ' predicates.

9.2.0.7.0sql> CREATE TABLE data_table          (ID     number          , data_type      varchar ()          , data           varchar ( ()          ); 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 ', ' n '); 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

Normal. Both the data type and the data column values are varchar strings, and no type conversions are required.
Experimental Statement 1:

Sql> Select ID from          data_table          where data_type= ' Zip '          and  to_number (data) =;  and To_number (data) =            *error at line 4:ora-01722:invalid number will prompt the processing of to_number with invalid numbers. Execute explain plan For,plan_table_ on it OUTPUT--------------------------------------------------------------------------------   1-filter (To_number ( "Data_table". " DATA ") =22 and              " data_table "." Data_type "= ' zip ')

you need to convert a varchar type field to a number type, and then compare it to a value of a numeric type. The conversion is valid for the 22 record in the data column, which contains the number numeric type, but the conversion is invalid for other rows because it does not contain equivalent values, such as ' Pet Foods Inc '. If a predicate comparison is to a row that contains a non-numeric type, a non-numeric type value and a numeric type value are required to be compared, and an error is made when the type conversion is done. If the predicate comparison starts with the ' data_type ' column and all rows containing non-numeric types are deleted, then no error is generated.
Experimental Statement 2:The following SQL, if the inline view is parsed first, all rows with data columns that contain non-numeric type values will be filtered.

Sql> Select ID from          (select ID, data from          data_table          where data_type= ' Zip '          )          where To_number ( data) =;        ID----------      1234 The subquery here only chooses the row data_type is a zip, and the corresponding data column value does not contain a non-number, so it can be executed normally. Execute explain plan For,plan_table_ on it OUTPUT--------------------------------------------------------------------------------   1-filter ("Data_ TABLE "." Data_type "= ' zip ' and              to_number (" data_table "." DATA ") =22)

once the subquery is parsed and filtered by data_type= ' zip ', the row data column of the result set is numeric, so To_number () can execute normally.
9i Let's look at the optimizer mode is RBO:

Sql> Show parameter Optimizername                                 TYPE                   VALUE---------------------------------------------------------- ------------------------------optimizer_mode                       string                 CHOOSE

if the statistics for the table are collected at this time, then the selection of the execution plan is affected.

sql> Analyze table data_table compute statistics; Table analyzed.

or change the session optimizer mode to CBO:

Sql> alter session set Optimizer_mode= ' All_rows '; Session altered.

get the following same conclusion. (Reason: If the table has statistics under RBO, the CBO will be used)

Experimental statement 3:sql> Select ID from          data_table          where data_type= ' Zip '          and  to_number (data) =          ;        ID----------      1234 This statement executes explain plan For,plan_table_ OUTPUT--------------------------------------------------------------------------------   1-filter ("Data_ TABLE "." Data_type "= ' zip ' and              to_number (" data_table "." DATA ") =22)

the data_type= ' Zip ' filter is used first and then To_number () is executed.
Experimental Statement 4:

Sql> Select ID from          (select ID, data from                   data_table                   where data_type= ' Zip '                   )          where To_number ( data) =;       where To_number (data) = *error at line             6:ora-01722:invalid number But now the second SQL executes an error. This statement executes explain plan for, and finds that the predicate condition becomes as follows (order changed): Plan_table_ OUTPUT--------------------------------------------------------------------------------   1-filter (To_number ( "Data_table". " DATA ") =22 and              " data_table "." Data_type "= ' zip ')

after the CBO or collect the statistics of the table, the To_number () will be executed first, then the data_type= ' Zip ' is used, so the error is not available.

11.2.0.1.0sql> CREATE TABLE data_table          (ID     number          , data_type      varchar)          , data           varchar ()          ); 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 ', ' n '); 1 row created. sql> INSERT into data_table values (1234, ' shipdate ', ' 03-oct-2003 '); 1 row created. Sql> commit; Commit complete. Optimizer mode is cbo:sql> show parameter optimizername                                 TYPE        VALUE-----------------------------------------------------------------------------optimizer_mode                       string      all_rows

Experimental Statement 5:

Sql> Select ID from          data_table          where data_type= ' Zip '          and  to_number (data) =;        ID----------      1234 Execution explain plan For,plan_table_ OUTPUT--------------------------------------------------------------------------------   1-filter ("Data_type "= ' zip ' and to_number (" DATA ") =22)

Experimental Statement 6:

Sql> Select ID from          (select ID, data from                   data_table                   where data_type= ' Zip '                   )         where To_number ( data) =;        ID----------      1234 Execution explain plan For,plan_table_ OUTPUT--------------------------------------------------------------------------------   1-filter ("Data_type "= ' zip ' and to_number (" DATA ") =22)

However, when the table collects statistics, the experimental statement 6 will error:

Sql> Select ID  2 from            (select ID, Data  3 from            data_table  4            where data_type= ' Zip '  5            )  6            where to_number (data) =;          where To_number (data) =                *error at line 6:ora-01722:invalid number using explain plan for, Plan_table_ OUTPUT--------------------------------------------------------------------------------   1-filter (to_number ("DATA") =22 and "data_type" = ' zip ')

This is true even with/*+ no_merge */.
Summary:1.9i, the optimizer default mode is Rbo, if the table does not have statistics, only follow the Rbo way, the SQL query statement according to the predicate from the right to the left of the order of resolution, such as experimental statement 1. 2. As described in @dbsnake book, Oracle will have a number of query translation rules built into it that Oracle will perform query transformations on as long as the target SQL meets the requirements of those rules. The query transformation in Oracle 9i is independent of the optimizer, independent of the optimizer type, because Oracle now thinks that the equivalent rewrite of SQL after query conversion is more efficient than the original target SQL execution. I guess the 9i built-in query transformation rules will first parse the subquery inline view, or the subquery expands after the condition is where to_number (data) = Data_type= ' zip ';, as in experimental statement 2, but only guess. 3.9i, if the table has statistics, or alter session to set the session-level optimizer mode for the CBO, as mentioned in MoS "theCBO" s function is to generate execution plans That's satisfy the most optimal it can.", experiment statement 3 indicates that the predicate order is adjusted by using the data_type= ' zip ' filter before executing to_number ( )。 4.9i under the CBO, as mentioned in the experimental statement 4,mos "thatmeans views often get merged into the main query and so the order in which predicate S is evaluated was not under your control", from the phenomenon to see first parse to_number (), resulting in an error. Guessing did a subquery unfold, the predicate condition is where data_type= ' Zip ' and to_number (data) = 22, but from 10053, there is no reason to see. 5.11g, the optimizer default mode is the CBO, which adjusts the predicate order, and the same effect as the CBO under 9i, such as experiment Statement 5. 6. Similarly, without the collection of table statistics, the experimental statement 6 can be performed normally. 7. @dbsnake mentioned in Oracle 10g and later, Oracle calculates the cost for some types of query conversions, only if the cost value of the equivalent rewrite SQL is less than the cost value of the original SQL that was not converted by the query. Oracle does not perform these query transformations on target SQL. After collecting the table statistic information, the experimental statement 5 can still execute normally, will follow the first parsing data_type= ' Zip ', after parsing to_number (), will not error. But the experimental statement 6 after the error, found that the predicate condition becomes the first parsing to_number (), so error, guess after collecting statistics, the execution cost of the experiment Statement 6 has changed, resulting in the use of different execution path, after collecting statistics, the experimental statement 6 cost value low execution path, Because the predicate first resolves the to_number (), resulting in an error. But no clue was seen from 10053.
Although some of the above suspicions, for the time being unclear, but at least from this article learned the following:1. The use of different versions of Rbo and CBO, and the impact on the execution of SQL. 2. Explain plan for observing the change in the order of the query statement predicates. 3.10053 View the cost selection for the execution plan.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MoS article experiment: ORA-01722 from Queries with Dependent predicates