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

小編給大家分享一下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無能為力的執行計劃”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！