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无能为力的执行计划”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！