导航：首页 > 数据库 >

SQL如何改变那些CBO无能为力的执行计划

发表于：2024-11-23 作者：千家信息网编辑

千家信息网最后更新 2024年11月23日，小编给大家分享一下SQL如何改变那些CBO无能为力的执行计划，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！用户写的 sql ， Oracle 会进行等价改写，即使是 RBO 优化模式

千家信息网最后更新 2024年11月23日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 matchedthen updateset a.ACCT_SKID = B.ACCT_SKID;

MERGE INTO 可以自由控制走嵌套循环或者走 hash 连接，并且当驱动表和被驱动表的使用数据超过 1G 时我们可以开启相应大小的并行 DML 更新。

merge /*+PARALLEL(8 )*/ into PRO_S_ACCT Ausing ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)when matchedthen updateset 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') xon (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' anda.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) JGMFROM 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) JGMfrom 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')) con 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无能为力的执行计划"有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！

很赞哦！