[Oracle] The View INVALID and cboinvalid brought by the CBO version

[Oracle] The View INVALID and cboinvalid brought by the CBO version

Source: Oracle 11.1.0.7

Target end: Oracle 11.2.0.4

In the CASE of a recent data migration, the status of a VIEW changes from VALID to INVALID after the data migration is completed.

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

Create or replace force view "APPS ". "dimensions" ("PROPOSAL_ID", "VERSION_ID", "dimensions", "LINE_ITEM_ID", "EXPENDITURE_TYPE", "dimensions", "BUDGET_CATEGORY_CODE", "BUDGET_CATEGORY", "BASE_AMT ", "OH_APPLIED_FLAG", "OH_COST", "EB_COST")

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. category;

Check the statement and find that the statement itself has problems. Theoretically, no database version can be run, and pbcv is missing in group. oh_cost, pbcv. the problem is that there is no problem in the source database (11.1.0.7), but there is a problem in the target database (11.2.0.4? At that time, I directly chose to think it was a BUG and changed the SQL statement, but I was always confused. These two days I finally figured out this problem, which was originally caused by the CBO version.

First, let's take a look at the execution plan of the SQL statement created for the view in the source database (11.1.0.7:

PLAN_TABLE_OUTPUT

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Bytes -------------------------------------------------------------------------------------------

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

Bytes -------------------------------------------------------------------------------------------

| 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) |

Bytes -------------------------------------------------------------------------------------------

 

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"> = SYSDATE @!) AND "START_DATE_ACTIVE" <= SYSDATE @!)

 

This query is actually based on the IGW_BUDGET_DETAILS and FND_LOOKUP_VALUES tables. After viewing other information, we found that the pbcv. oh_cost and pbcv. eb_cost columns not added to the group by SQL statement are the results obtained after line_item_id is processed in the IGW_BUDGET_DETAILS column.

In this case, we guess whether the SQL statement can be successfully executed in the source database (11.1.0.7) is because after the SQL statement performs a syntax check, the CBO performs query conversion on the SQL statement, the select statement in the final conversion result does not contain pbcv. oh_cost, pbcv. eb_cost, but the information of the line_item_id column. Therefore, the statement is successfully executed. In the target database (11.2.0.4), different CBO versions cause different query conversion results.

Next, modify the CBO version of the target database and execute the statement:

SQL> alter system set optimizer_features_enable = '11. 1.0.7 ';

 

System altered.

 

SQL> CREATE OR REPLACE FORCE VIEW "APPS ". "dimensions" ("PROPOSAL_ID", "VERSION_ID", "dimensions", "LINE_ITEM_ID", "EXPENDITURE_TYPE", "dimensions", "BUDGET_CATEGORY_CODE", "BUDGET_CATEGORY", "BASE_AMT ", "OH_APPLIED_FLAG", "OH_COST", "EB_COST")

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

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. expendre_type, pbcv. category;

 

View created.

 

You can see that the view is successfully created after the CBO version is downgraded, which is caused by the CBO version.

Of course, the final solution is to rewrite the SQL statement, but finally figured out the cause. It is not a BUG, but because the CBO version is different and the query conversion result is different. I would like to record it here and explain it to the brothers who have encountered the same problem ~