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

創(chuàng)新互聯(lián)建站是專業(yè)的平遠(yuǎn)網(wǎng)站建設(shè)公司，平遠(yuǎn)接單;提供成都做網(wǎng)站、網(wǎng)站設(shè)計(jì),網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行平遠(yuǎn)網(wǎng)站開發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!

用戶寫的 sql ， Oracle 會(huì)進(jìn)行等價(jià)改寫，即使是 RBO 優(yōu)化模式， Oracle 也會(huì)給你做一些轉(zhuǎn)換，這些轉(zhuǎn)化都是基于一種固定的算法， oracle 稱這種轉(zhuǎn)換是“啟發(fā)式”的。比如我們寫 inner  join 時(shí) ，并且只訪問單表數(shù)據(jù) ， Oracle 會(huì)自動(dòng)降為半連接，然后用 semi join 的方式給你做 join 。 transformation 是 Oracle 必做的一個(gè)步驟，至少在 8.05 版本之后 transformation 都一直存在。

網(wǎng)上有很多優(yōu)化法則，有的說(shuō) exists 比 in 效率高，有的說(shuō) in 比 exists 執(zhí)行的快，那就要看 SQL 是如何寫的， CBO是如何轉(zhuǎn)換的，是否能轉(zhuǎn)換？當(dāng)然這種轉(zhuǎn)換不是基于成本的而是“基于啟發(fā)的轉(zhuǎn)化”。

當(dāng) Oracle 沒辦法做 transformation 的時(shí)候，可能就是 sql 產(chǎn)生問題的時(shí)候，此時(shí)就要我們?nèi)フ以蛄?，下面通過一些案例，說(shuō)明這種優(yōu)化器無(wú)能為力的情況（為了保護(hù)客戶的隱私，表名和部分列已經(jīng)重命名）。

|   用 merge 代替 update

UPDATE 關(guān)聯(lián)更新跑了將近 40分鐘 ， SQL 語(yǔ)句如下：

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

執(zhí)行計(jì)劃如下： 

查看量表數(shù)據(jù)量， 其中 PRO_S_ACCT 有 1044227 行數(shù)據(jù)， acct_s_bk 有 553554 行數(shù)據(jù)。

UPDATE 后面跟子查詢類似嵌套循環(huán)。 pro_s_acct 為嵌套循環(huán)的驅(qū)動(dòng)表 ， acct_s_bk 為被驅(qū)動(dòng)表 ，那么表 acct_s_bk就會(huì)被掃描 100 多萬(wàn)次，就會(huì)產(chǎn)生大量的邏輯讀，被驅(qū)動(dòng)表走全表掃描，我們可以在其上面建立索引 ，但是此時(shí)索引會(huì)被掃描 100 多萬(wàn)次。

下面我們建立索引看其執(zhí)行計(jì)劃如下：

create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID);

下面我們通過用 merge into  等價(jià)改寫 看其執(zhí)行計(jì)劃：

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 可以自由控制走嵌套循環(huán)或者走 hash 連接，并且當(dāng)驅(qū)動(dòng)表和被驅(qū)動(dòng)表的使用數(shù)據(jù)超過 1G 時(shí)我們 可以開啟相應(yīng)大小的并行 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;

實(shí)際執(zhí)行 中， 2s 完成。

下面通過 sql 改寫，來(lái)讓 sql 的執(zhí)行計(jì)劃被我們所控制。

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表是按天分區(qū)，分區(qū)字段是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');

|  有關(guān)外鏈接的其他改寫 

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

由于環(huán)境是跑批業(yè)務(wù)，建立索引需要全面考慮，為了不改變當(dāng)前環(huán)境我們盡量不建立索引，執(zhí)行計(jì)劃如下： 

實(shí)際執(zhí)行時(shí)間 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

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

看完了這篇文章，相信你對(duì)“SQL如何改變那些CBO無(wú)能為力的執(zhí)行計(jì)劃”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！