Oracle Database 12c查詢最佳化器的缺陷-檢視合併會造成查詢結果不準確
最近在將一個11g的資料庫匯入到12c(12.1.0.2,並打了最新的補丁)的庫後,測試人員反饋有一個SQL執行結果不正確。
具體的SQL如下:
由於第一個子查詢(我們姑且把它稱為C子查詢)涉及到DBLINK,所以一開始懷疑DBLINK不一致導致的。但是檢視了DBLINK的配置後發現和源11g的配置是一樣的,連的都是相同的資料庫。
試著單獨C子查詢,在11g和12c中的執行結果是一樣的!
接著試著把C子查詢的結果做成一張表ctemp,用ctemp代替C子查詢,即:
這時在11g和12c中的執行結果是一樣的。
接著試著把C子查詢的結果做成一張試圖cvtemp,用cvtemp代替C子查詢,即:
查詢的結果出現差異了。仔細觀察執行計劃,似乎12c中的執行計劃有異常,NOT EXISTS這個條件居然沒有在執行計劃中體現!因為這是在用檢視的情況下發生的,所以有理由懷疑12c的最佳化器在檢視合併上是有異常的,那把檢視合併禁用掉會是什麼情況?
這裡透過HINT禁用了檢視合併,執行結果和11g是一樣的了。然後觀察執行計劃,非常明顯的在禁用了檢視合併後多了NOT EXISTS條件的過濾:
filter( NOT EXISTS (SELECT 0 FROM "POLICYINFO" "POLICYINFO" WHERE "PROPOSALCONTNO"=:B1))
而在之前的執行計劃中是沒有這個過濾的。
考慮到C子查詢是個複雜檢視,所以嘗試在系統層面禁用了複雜檢視的合併:
再執行上述語句時,即使不加HINT也能執行出正確的結果了。
簡單檢視合併會不會也存在上述類似的問題?這個還有待驗證。
12c引入了很多吸引人的功能,比如對租戶,比如記憶體資料庫,還有更強大的最佳化器。但是首要保證的是執行出正確的結果,如果這都無法保證了,那一切都要成為浮雲了。
具體的SQL如下:
-
SELECT *
-
FROM ( SELECT watnum,
-
agentcode,
-
managecom,
-
SUM (prem) AS prem,
-
SUM (charge) AS charge
-
FROM (SELECT ct.card_number,
-
ct.card_managecode,
-
cp.riskcode,
-
cp.riskname,
-
cp.prem,
-
csm.watnum,
-
csm.charge,
-
SUBSTR (csm.agentcode, 2) AS agentcode,
-
SUBSTR (csm.managecom, 2) AS managecom
-
FROM card_table@webapp ct,
-
ZZHCardSettleMent csm,
-
CARDPREMIUM@WEBAPP cp
-
WHERE ct.card_type = cp.cardtype
-
AND csm.idcard = ct.card_number
-
AND csm.checktype = 1
-
AND ct.card_managecode = '8602')
-
GROUP BY watnum, agentcode, managecom) c
-
WHERE NOT EXISTS
-
(SELECT 'Y'
-
FROM policyinfo
- WHERE c.watnum = proposalcontno);
試著單獨C子查詢,在11g和12c中的執行結果是一樣的!
接著試著把C子查詢的結果做成一張表ctemp,用ctemp代替C子查詢,即:
-
SELECT *
-
FROM ctemp c
-
WHERE NOT EXISTS
-
(SELECT 'Y'
-
FROM policyinfo
- WHERE c.watnum = proposalcontno);
接著試著把C子查詢的結果做成一張試圖cvtemp,用cvtemp代替C子查詢,即:
-
SELECT *
-
FROM cvtemp c
-
WHERE NOT EXISTS
-
(SELECT 'Y'
-
FROM policyinfo
- WHERE c.watnum = proposalcontno);
-
SELECT /*+ NO_MERGE(c) */ *
-
FROM cvtemp c
-
WHERE NOT EXISTS
-
(SELECT 'Y'
-
FROM policyinfo
- WHERE c.watnum = proposalcontno);
filter( NOT EXISTS (SELECT 0 FROM "POLICYINFO" "POLICYINFO" WHERE "PROPOSALCONTNO"=:B1))
而在之前的執行計劃中是沒有這個過濾的。
考慮到C子查詢是個複雜檢視,所以嘗試在系統層面禁用了複雜檢視的合併:
- alter system set "_complex_view_merging"=false;
簡單檢視合併會不會也存在上述類似的問題?這個還有待驗證。
12c引入了很多吸引人的功能,比如對租戶,比如記憶體資料庫,還有更強大的最佳化器。但是首要保證的是執行出正確的結果,如果這都無法保證了,那一切都要成為浮雲了。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/13885898/viewspace-2140360/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Database 12c查詢最佳化器的缺陷-外連線導致結果不準確OracleDatabase
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- Oracle查詢轉換(一)簡單檢視合併Oracle
- Oracle查詢轉換(二)複雜檢視合併Oracle
- Oracle查詢轉換(三)外連線檢視合併Oracle
- mysql中的多行查詢結果合併成一個MySql
- 水煮oracle31----連線查詢&合併查詢Oracle
- 合併查詢
- Oracle 12CR2查詢轉換之檢視合併Oracle
- 等於NULL的查詢條件導致查詢結果不正確Null
- oracle 常用查詢檢視Oracle
- oracle 精確查詢和模糊查詢Oracle
- mysql查詢結果多列拼接查詢MySql
- oracle 查詢結果的各種格式Oracle
- 【SQL優化器查詢變換器】檢視合併(View Merging)SQL優化View
- oracle查詢結果外面新增引號Oracle
- Oracle查詢結果 儲存為XMLOracleXML
- MySQL 合併查詢union 查詢出的行合併到一個表中MySql
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- arcgis api for javascript學習-使用FeatureLayer結合FeatureTable實現查詢並能根據查詢結果將檢視縮放至結果的範圍APIJavaScript
- 【PDB】Oracle跨PDB檢視查詢Oracle
- MySQL 合併查詢join 查詢出的不同列合併到一個表中MySql
- xsl中,對xml文件查詢的結果再次查詢XML
- oracle的查詢最佳化Oracle
- SQL Server對組合查詢結果排序方法SQLServer排序
- navicat 匯出查詢結果
- mysql匯出查詢結果MySql
- 【Oracle】如何查詢檢視時使用索引Oracle索引
- SGA中Latch 的分類和查詢--結合v$latch檢視
- 檢視查詢報錯
- 【MySQL】檢視&子查詢MySql
- MySQL pager和nopager命令--不顯示查詢結果MySql
- Oracle最佳化技術---物化檢視查詢重寫query rewriteOracle
- mysql求交集:UNION ALL合併查詢,inner join內連線查詢,IN/EXISTS子查詢MySql
- MongoDB的排除查詢$ne缺陷MongoDB
- [Mysql 查詢語句]——對查詢結果進一步的操作MySql
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- 使用查詢結果更新表的方法