程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【Oracle】CBO版本帶來的視圖INVALID

【Oracle】CBO版本帶來的視圖INVALID

編輯:Oracle教程

【Oracle】CBO版本帶來的視圖INVALID


源端:Oracle 11.1.0.7

目標端:Oracle 11.2.0.4

最近做的一個數據遷移的CASE中遇到的問題,一個VIEW在完成數據遷移後由VALID的狀態變為了INVALID。

從源庫中使用get_ddl拿到的該VIEW創建語句如下:

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;

拿到該語句進行了查看發現該語句本身就存在問題,理論上無論哪個數據庫版本都無法運行才對,在group by當中缺少了pbcv.oh_cost 、pbcv.eb_cost兩列,但問題就是為何在源庫(11.1.0.7)當中不存在問題,而目標庫(11.2.0.4)當中存在問題呢?當時做case的時候直接選擇了認為是一個BUG,改寫了SQL,但一直心存疑惑。這兩天終於搞明白了這個問題,原來是由於CBO版本導致的。

首先我們來看一下源庫(11.1.0.7)版本中創建view的該sql語句的執行計劃:

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

 

該查詢實際上是基於兩個表IGW_BUDGET_DETAILS、FND_LOOKUP_VALUES 的查詢。經過查看其它信息發現sql語句中未加入到group by當中的pbcv.oh_cost 、pbcv.eb_cost兩列均為對於表IGW_BUDGET_DETAILS中列line_item_id處理後得到的結果。

針對這種情況進行猜想,該sql語句在源庫(11.1.0.7)能成功執行的原因是否是因為:在sql語句進行語法檢查後CBO對該sql語句進行查詢轉換,最終轉換結果中的select語句中並不包含pbcv.oh_cost 、pbcv.eb_cost,而是line_item_id列的信息,所以該語句最終執行成功。而在目標庫(11.2.0.4)中由於CBO版本不同,查詢轉換結果不同導致了該問題。

那麼下面嘗試一下修改目標庫的CBO版本,然後執行該語句:

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.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版本不同,查詢轉換結果不同導致的。在此記錄一下,也希望能給遇到同樣問題的兄弟解解惑~

 

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved