小编给大家分享一下SQL如何改变那些CBO无能为力的执行计划，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

用户写的sql，Oracle会进行等价改写，即使是RBO优化模式，Oracle也会给你做一些转换，这些转化都是基于一种固定的算法，oracle称这种转换是“启发式”的。比如我们写innerjoin时，并且只访问单表数据，Oracle会自动降为半连接，然后用semi join的方式给你做join。transformation是Oracle必做的一个步骤，至少在8.05版本之后transformation都一直存在。

网上有很多优化法则，有的说exists比in效率高，有的说in比exists执行的快，那就要看SQL是如何写的，CBO是如何转换的，是否能转换？当然这种转换不是基于成本的而是“基于启发的转化”。

当Oracle没办法做transformation的时候，可能就是sql产生问题的时候，此时就要我们去找原因了，下面通过一些案例，说明这种优化器无能为力的情况（为了保护客户的隐私，表名和部分列已经重命名）。

|用merge代替update

UPDATE关联更新跑了将近40分钟，SQL语句如下：

UPDATEPRO_S_ACCTASETACCT_SKID=(SELECTACCT_SKIDFROMACCT_S_BKBWHEREA.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多万次。

下面我们建立索引看其执行计划如下：

createindexind_id_skidonacct_s_bk(ACCT_ID,ACCT_SKID);

下面我们通过用merge into等价改写看其执行计划：

mergeintoPRO_S_ACCTAusingACCT_S_BKBon(A.ACCT_ID=B.ACCT_ID)whenmatchedthenupdateseta.ACCT_SKID=B.ACCT_SKID;


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

merge/*+PARALLEL(8)*/intoPRO_S_ACCTAusingACCT_S_BKBon(A.ACCT_ID=B.ACCT_ID)whenmatchedthenupdateseta.ACCT_SKID=B.ACCT_SKID;

实际执行中，2s完成。

下面通过sql改写，来让sql的执行计划被我们所控制。

UPDATEINXXISET(I.INT_FRM_DT,I.INT_TO_DT,I.ACCT_DESC)=(SELECTDBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESCFROMDBPPWHEREDBPP.SYS_ID='INV'ANDDBPP.ACCT_TYPE=I.ACCT_TYPEANDDBPP.INT_CAT=I.INT_CAT)WHEREI.EXTDATE=TO_DATE('2018-04-03','YYYY-MM-DD')ANDEXISTS(SELECTDBPP.SYS_IDFROMDBPPWHEREDBPP.SYS_ID='INV'ANDDBPP.ACCT_TYPE=I.ACCT_TYPEANDDBPP.INT_CAT=I.INT_CATANDDBPP.ACCT_DESC='S');


merge/*+parallel(10)use_hash(I,X)swap_join_inputs(X)*/intoINXXIusing(SELECTDBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC,DBPP.ACCT_TYPE,DBPP.INT_CATFROMDBPPWHEREDBPP.SYS_ID='INV'ANDDBPP.ACCT_DESC='S')xon(x.ACCT_TYPE=I.ACCT_TYPEANDx.INT_CAT=I.INT_CAT)whenmatchedthenupdatesetI.INT_FRM_DT=x.CR_SOP_DATE,I.INT_TO_DT=x.EOP_DATE,I.ACCT_DESC=x.ACCT_DESCWHEREI.EXTDATE=TO_DATE('2018-04-03','YYYY-MM-DD');


另一类似案例：

updateWWWaseta.cny_bal=a.ll_bal*nvl((selectb.hlfromMMMbwhereb.startdate<=a.extedateandb.enddate>a.extdateandb.zb='CNY'anda.curr=b.yb),0)wherea.extdate=to_date('2018-04-01','yyyy-mm-dd');由于www表是按天分区，分区字段是extdate，那么可以起改写成如下：merge/*+parallel(8)*/intowwwausing(selectb.hlfromMMMbwhereb.zb='CNY'andb.enddate>date'2018-04-01'andb.startdate<=date'2018-04-01')con(a.curr=c.yb)whenmatchedthenupdateseta.cny_bal=a.ll_bal*NVL(c.hl,0)wherea.extdate=to_date('2018-04-01','yyyy-mm-dd');

|有关外链接的其他改写

SELECTCASEWHENNOTEXISTS(SELECTKHHFROMNB_XXXXXXXXBWHERERQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12)ANDRQ<=TO_DATE('2018-04-27','YYYY-MM-DD')ANDA.CUSTNO=B.KHH)ANDA.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD')THENA.CUSTNOENDBQXZ,CASETHENNOTEXISTS(SELECTKHHFROMNB_XXXXXXXXBWHERERQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12)ANDRQ<=TO_DATE('2018-04-27','YYYY-MM-DD')ANDA.CUSTNO=B.KHH)ANDA.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD')THENA.CUSTNOENDYE,'2'ASQD,SUBSTR(B.OPENBANKNO,1,4)JGMFROMNB_CCCCCCCCCAinnerjoinDZZH_XXXXXXXXXXXXXXXXXXBONA.CUSTNO=B.CUSTNOWHERECUPCHECKSTTIN('1','2');685012rowsselected

由于环境是跑批业务，建立索引需要全面考虑，为了不改变当前环境我们尽量不建立索引，执行计划如下：

实际执行时间37分钟完成。

SELECTCASEWHENc.khhisnullthenA.CUSTNOENDBQXZ,CASEWHENc.khhisnullthenA.CUSTNOENDye,'2'ASQD,SUBSTR(B.OPENBANKNO,1,4)JGMfromNB_CCCCCCCCCAinnerjoinDZZH_XXXXXXXXXXXXXXXXXXBONA.CUSTNO=B.CUSTNOleftjoin(SELECTKHHFROMNB_XXXXXXXXBWHERERQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12)ANDRQ<=TO_DATE('2018-04-27','YYYY-MM-DD'))conA.CUSTNO=c.KHHandA.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD')whereCUPCHECKSTTIN('1','2');685012rowsselected

执行计划如下，并且NB_XXXXXXXX表只扫描一次，逻辑读由84M+18M降为126，执行时间也降为秒级（当然下面的数据因多次执行已经在buffer中）。

看完了这篇文章，相信你对“SQL如何改变那些CBO无能为力的执行计划”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！