|
L> 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.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.expenditure_type ,pbcv.expenditure_category_flag;
View created.
可以看到在降低CBO版本后视图成功创建，果然是CBO版本导致的。
当然这个最终的解决方法还是要改写SQL，但终于弄明白了其导致原因，并不是什么BUG，而是由于CBO版本不同，查询转换结果不同导致的。在此记录一下，也希望能给遇到同样问题的兄弟解解惑~
?
?
|