SQL如何改變那些CBO無能為力的執行計劃

174次閱讀

共計 4060 個字符，預計需要花費 11 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 SQL 如何改變那些 CBO 無能為力的執行計劃，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

用戶寫的 sql ， Oracle 會進行等價改寫，即使是 RBO 優化模式， Oracle 也會給你做一些轉換，這些轉化都是基于一種固定的算法， oracle 稱這種轉換是“啟發式”的。比如我們寫 inner join 時，并且只訪問單表數據， Oracle 會自動降為半連接，然后用 semi join 的方式給你做 join 。 transformation 是 Oracle 必做的一個步驟，至少在 8.05 版本之后 transformation 都一直存在。

網上有很多優化法則，有的說 exists 比 in 效率高，有的說 in 比 exists 執行的快，那就要看 SQL 是如何寫的， CBO 是如何轉換的，是否能轉換？當然這種轉換不是基于成本的而是“基于啟發的轉化”。

當 Oracle 沒辦法做 transformation 的時候，可能就是 sql 產生問題的時候，此時就要我們去找原因了，下面通過一些案例，說明這種優化器無能為力的情況（為了保護客戶的隱私，表名和部分列已經重命名）。

| 用 merge 代替 update

UPDATE 關聯更新跑了將近 40 分鐘， SQL 語句如下：

UPDATE PRO_S_ACCT A SET ACCT_SKID = (SELECT ACCT_SKID FROM ACCT_S_BK B WHERE A.ACCT_ID = B.ACCT_ID);

執行計劃如下：

查看量表數據量，其中 PRO_S_ACCT 有 1044227 行數據， acct_s_bk 有 553554 行數據。

UPDATE 后面跟子查詢類似嵌套循環。 pro_s_acct 為嵌套循環的驅動表， acct_s_bk 為被驅動表，那么表 acct_s_bk 就會被掃描 100 多萬次，就會產生大量的邏輯讀，被驅動表走全表掃描，我們可以在其上面建立索引，但是此時索引會被掃描 100 多萬次。

下面我們建立索引看其執行計劃如下：

create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID);

下面我們通過用 merge into 等價改寫看其執行計劃：

merge into PRO_S_ACCT A 
using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
when matched
then update
set a.ACCT_SKID = B.ACCT_SKID;

MERGE INTO 可以自由控制走嵌套循環或者走 hash 連接，并且當驅動表和被驅動表的使用數據超過 1G 時我們可以開啟相應大小的并行 DML 更新。

merge /*+PARALLEL(8 )*/ into PRO_S_ACCT A
using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
when matched
then update
set a.ACCT_SKID = B.ACCT_SKID;

實際執行中， 2s 完成。

下面通過 sql 改寫，來讓 sql 的執行計劃被我們所控制。

UPDATE INXX I 
SET (I.INT_FRM_DT,I.INT_TO_DT,I.ACCT_DESC) = (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC
 FROM DBPP
 WHERE DBPP.SYS_ID= INV 
 AND DBPP.ACCT_TYPE = I.ACCT_TYPE
 AND DBPP.INT_CAT = I.INT_CAT)
WHERE I.EXTDATE = TO_DATE(2018-04-03 , YYYY-MM-DD)
AND EXISTS (SELECT DBPP.SYS_ID
 FROM DBPP
 WHERE DBPP.SYS_ID= INV 
 AND DBPP.ACCT_TYPE = I.ACCT_TYPE
 AND DBPP.INT_CAT = I.INT_CAT
 AND DBPP.ACCT_DESC =  S

merge /*+parallel(10) use_hash(I,X) swap_join_inputs(X)*/ into INXX I 
using (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC,DBPP.ACCT_TYPE,DBPP.INT_CAT FROM DBPP WHERE DBPP.SYS_ID= INV  AND DBPP.ACCT_DESC =  S) x
on (x.ACCT_TYPE = I.ACCT_TYPE AND x.INT_CAT = I.INT_CAT) 
when matched 
then update set I.INT_FRM_DT=x.CR_SOP_DATE,I.INT_TO_DT=x.EOP_DATE,I.ACCT_DESC=x.ACCT_DESC 
WHERE I.EXTDATE = TO_DATE(2018-04-03 , YYYY-MM-DD

另一類似案例：

update WWW a 
set a.cny_bal=a.ll_bal*nvl((select b.hl from MMM b where b.startdate =a.extedate and b.enddate   a.extdate and b.zb= CNY  and
a.curr=b.yb),0)
where a.extdate=to_date( 2018-04-01 , yyyy-mm-dd 
由于 www 表是按天分區，分區字段是 extdate，那么可以起改寫成如下：merge /*+parallel(8)*/ into www a 
using (select b.hl from MMM b where b.zb= CNY  and b.enddate date 2018-04-01  and b.startdate =date 2018-04-01) c 
on (a.curr=c.yb) 
when matched 
then update 
set a.cny_bal=a.ll_bal*NVL(c.hl,0) 
where a.extdate=to_date(2018-04-01 , yyyy-mm-dd

| 有關外鏈接的其他改寫

SELECT 
CASE WHEN 
 NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD) AND A.CUSTNO=B.KHH) 
 AND A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) THEN A.CUSTNO END BQXZ,
CASE THEN 
 NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD) AND A.CUSTNO=B.KHH) 
 AND A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) THEN A.CUSTNO END YE,
 2  AS QD,
SUBSTR(B.OPENBANKNO,1,4) JGM
FROM NB_CCCCCCCCC A 
inner join DZZH_XXXXXXXXXXXXXXXXXX B 
ON A.CUSTNO = B.CUSTNO 
WHERE CUPCHECKSTT IN ( 1 , 2 
685012 rows selected

由于環境是跑批業務，建立索引需要全面考慮，為了不改變當前環境我們盡量不建立索引，執行計劃如下：

實際執行時間 37 分鐘完成。

SELECT 
CASE WHEN c.khh is null then A.CUSTNO END BQXZ,
CASE WHEN c.khh is null then A.CUSTNO END ye,
 2  AS QD,
SUBSTR(B.OPENBANKNO,1,4) JGM
from NB_CCCCCCCCC A 
inner join DZZH_XXXXXXXXXXXXXXXXXX B 
ON A.CUSTNO = B.CUSTNO 
left join 
(SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD)) c
on A.CUSTNO=c.KHH and A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) 
where CUPCHECKSTT IN ( 1 , 2 
685012 rows selected

執行計劃如下，并且 NB_XXXXXXXX 表只掃描一次，邏輯讀由 84 M + 18M 降為 126 ，執行時間也降為秒級（當然下面的數據因多次執行已經在 buffer 中）。

看完了這篇文章，相信你對“SQL 如何改變那些 CBO 無能為力的執行計劃”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完