View of the "Oracle" CBO version invalid

SOURCE End: Oracle 11.1.0.7

Target end:Oracle 11.2.0.4

A recent data migration case encountered in the case of a VIEW after the data migration completed by the VALID State changed to INVALID.

the VIEW creation statement obtained from the source library using get_ddl is as follows:

CREATE OR REPLACE Force VIEW "APPS". Igw_budget_category_v1 "(" proposal_id "," version_id "," budget_period_id "," line_item_id "," Expenditure_type "," Expenditure_category_flag "," Budget_category_code "," Budget_category "," Base_amt "," Oh_applied_flag "," OH_COST "," EB _cost ") as

SELECT pbcv.proposal_id, pbcv.version_id, pbcv.budget_period_id, pbcv.line_item_id, Pbcv.expenditure_type, Pbcv.expenditure_category_flag, Pbcv.budget_category_code, fl.meaning budget_category, sum (pbcv.line_item_cost+ Pbcv.eb_cost) Base_amt, decode (pbcv.oh_cost, 0, ' N ', ' Y ') Oh_applied_flag, Pbcv.oh_cost, Pbcv.eb_cost

from Igw_budget_complete_v PBCV, Igw_lookups_v FL

WHERE pbcv.budget_category_code = fl.lookup_code and Fl.lookup_type = ' igw_budget_category '

group by pbcv.proposal_id, pbcv.version_id, pbcv.budget_period_id, Pbcv.budget_category_code, fl.meaning, pbcv.line_item_id, Pbcv.expenditure_type, Pbcv.expenditure_category_flag;

The

Gets the statement to see that the statement itself has a problem, theoretically no matter which version of the database can not be run, in group by Span style= "font-family: Arial" is missing the PBCV.OH_COST  Span style= "font-family: Arial" >, pbcv.eb_cost two columns, but the question is why in the Source library ( 11.1.0.7 11.2.0.4 case when the direct choice to think is a bug , rewritten sql CBO Version is caused.

First, let's take a look at the execution plan for the SQL statement that created the view in the Source library (11.1.0.7) version :

Plan_table_output

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |

-------------------------------------------------------------------------------------------

| 0 |                      SELECT STATEMENT |     |   1 |     156 | 5 (20) |

|  1 |                      HASH GROUP by |     |   1 |     156 | 5 (20) |

|   2 |                      NESTED LOOPS |       |       |            | |

|    3 |                      NESTED LOOPS |     |   1 |     156 | 4 (0) |

|     4 | TABLE ACCESS Full |     Igw_budget_details |    1 |     88 | 2 (0) |

|* 5 | INDEX RANGE SCAN |     FND_LOOKUP_VALUES_U1 |       1 |     | 2 (0) |

|* 6 | TABLE ACCESS by INDEX rowid|     Fnd_lookup_values |    1 |     68 | 2 (0) |

-------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

5-access ("lookup_type" = ' igw_budget_category ' and "view_application_id" =0 and

"Budget_category_code" = "Lookup_code" and "LANGUAGE" =userenv (' LANG '))

Filter ("LANGUAGE" =userenv (' LANG ') and

"security_group_id" = "Fnd_global". " Lookup_security_group "(" LV ".) Lookup_type "," LV "." V

iew_application_id "))

6-filter ("enabled_flag" = ' Y ' and ("End_date_active" is NULL OR

"End_date_active" >[email protected]!) and "Start_date_active" <[email protected]!)

The query is actually based on a query of two tables igw_budget_details,fnd_lookup_values . After viewing other information found in SQL statements that are not included in the group by pbcv.oh_cost , Pbcv.eb_cost Two columns are for columns in table igw_budget_details line_item_id The results obtained after processing.

In response to this scenario, theSQLstatement in the Source library (11.1.0.7whether the reason for successful execution is because:SQLstatement after the syntax checkCBOon theSQLstatement for the query transformation, the final conversion result of theSelectThe statement does not containPbcv.oh_cost,Pbcv.eb_cost, butline_item_idcolumn, so the statement eventually executes successfully. And in the target library (11.2.0.4) because ofCBODifferent versions of the query result in different results that cause the problem.

Then try modifying the CBO version of the target library , and then execute the statement:

Sql> alter system set optimizer_features_enable= ' 11.1.0.7 ';

System altered.

sql> CREATE OR REPLACE Force VIEW "APPS". Igw_budget_category_v1 "(" proposal_id "," version_id "," budget_period_id "," line_item_id "," Expenditure_type "," Expenditure_category_flag "," Budget_category_code "," Budget_category "," Base_amt "," Oh_applied_flag "," OH_COST "," EB _cost ") as

SELECT pbcv.proposal_id, pbcv.version_id, pbcv.budget_period_id, pbcv.line_item_id, Pbcv.expenditure_type, Pbcv.expe Nditure_category_flag, Pbcv.budget_category_code, fl.meaning budget_category, sum (pbcv.line_item_cost+pbcv.eb_cost ) Base_amt, decode (pbcv.oh_cost, 0, ' N ', ' Y ') Oh_applied_flag, Pbcv.oh_cost, Pbcv.eb_cost

2 3 from Igw_budget_complete_v PBCV, Igw_lookups_v FL

4 WHERE Pbcv.budget_category_code = fl.lookup_code and Fl.lookup_type = ' igw_budget_category '

5 GROUP by pbcv.proposal_id, pbcv.version_id, pbcv.budget_period_id, Pbcv.budget_category_code, fl.meaning, Pbcv.line_ ITEM_ID, Pbcv.expenditure_type, Pbcv.expenditure_category_flag;

View created.

You can see that the view was successfully created after lowering the CBO version, which was due to the CBO version.

Of course, the final solution is to rewrite the SQL, but finally understand the cause, not a BUG, but because the CBO version is different, Result of different query conversion results. In this record, but also want to be able to meet the same problems of the brothers to solve the problem ~

View of the "Oracle" CBO version invalid