这是一次快速优化的小case，欢迎喜欢SQL的各位朋友看看放松下。
本周给事业部出报表时,发现了业务问题数据，经过与负责的研发同学沟通后，获得了研发同学提供的问题数据修复SQL(只提取了有问题的部分):
update arc_mgr_detail t set t.dossier_count_t=(select max(d.check_count) from arc_apply_detail d
where t.is_del=0 and d.is_del=0 and t.contract_no=d.contract_no and t.paylist_code=d.paylist_code
and t.arc_receipt_detail_id=d.arc_receipt_detail_id
and t.dossier_count_t=0 and d.check_count>0)
where t.id in (select t.id from arc_mgr_detail t ,arc_apply_detail d
where t.is_del=0 and d.is_del=0 and t.contract_no=d.contract_no and t.paylist_code=d.paylist_code
and t.arc_receipt_detail_id=d.arc_receipt_detail_id
and t.dossier_count_t=0 and d.check_count>0);
执行计划如下：
执行了10分钟没有跑出来,眉头一皱，好玩了, 又到了优化时刻！
之前记得在公司SQL培训中多次说过，关联更新百万级以下数据，update直接改成merge into写法就好了,说干就干
(记住MERGE INTO 基础语法，不用看SQL业务逻辑 直接套)
merge into (select arc_receipt_detail_id,contract_no,dossier_count_t,t.paylist_code
from arc_mgr_detail t where t.id (select t1.id
from arc_mgr_detail t1, arc_apply_detail d1
where t1.is_del = 0
and d1.is_del = 0
and t1.contract_no = d1.contract_no
and t1.paylist_code = d1.paylist_code
and t1.arc_receipt_detail_id = d1.arc_receipt_detail_id
and t1.dossier_count_t = 0
and d1.check_count > 0)
) t3
using (select max(d2.check_count) MCOUNT ,D2.contract_no,D2.arc_receipt_detail_id,D2.paylist_code
from arc_apply_detail d2, arc_mgr_detail t2
where t2.is_del = 0
and d2.is_del = 0
and t2.contract_no = d2.contract_no
and t2.paylist_code = d2.paylist_code
and t2.arc_receipt_detail_id = d2.arc_receipt_detail_id
and t2.dossier_count_t = 0
and d2.check_count > 0
group by D2.contract_no,D2.arc_receipt_detail_id,D2.paylist_code) d3
on (t3.contract_no = d3.contract_no and t3.arc_receipt_detail_id = d3.arc_receipt_detail_id
and t3.paylist_code = d3.paylist_code)
when matched then
update set t3.dossier_count_t =d3.MCOUNT;
咔咔咔 几分钟改完,一副好像没问题的样子,让我们先看一眼改完后的执行计划：
什么，TIME居然999？ 虽然是假的，大部分情况下还是能说明这个SQL的性能糟糕程度的。
抱着万一的希望，执行了一波。
果然，7分钟还没跑完，奇迹木有发生。是时候认真看下了：
首先，让我们拆一波 看看数据量：
要修改的数据只有568条，查询速度还凑合(只针对本次一次性更新操作而言)，去掉统计后的执行计划如下:
可以看出都是全表扫描速度依然很快。
下面对应的条件也只有564条，拆开来每个部分速度都很快。
去掉统计后的执行计划如下：
这时候，对比前面time 999的执行计划，老司机们基本都看出问题所在了：
这一步计算出现的性能问题，可以看到他没有谓词信息，那么要看它到底对应SQL哪一步，我们就需要明白整体SQL的表检索链接顺序：
首先是d3里面的表先行计算完毕，8976105 形成一个视图
然后是t表传值到t1表(这就是隐含谓词推入)，然后t1表跟d1表进行hash关联 14 13 15 12
接下来是t1与d1的关联结果集作为谓词推入前面的视图中先行计算11
最后就是计算完成的结果跟t表进行NL半连接更新啦
说到这里，老司机们基本很明确了。是的 就是传值搞的事儿，t表的值在执行计划里面居然被估算成1了！(当然。只是估测，这一波如果不准，就需要AE-ROWS执行计划干了，还好。这里很顺利)
那么如何阻止他们之间的传值关系呢？
一般的人直接就 NO_MERGE/NO_UNNEST干一下，想着 让T1跟D1自己先关联 别直接找T关联了，思路是好的。
然而, ORACLE的CBO这时候很[机智]，你rows预估=1 我一定要主键传值进来。
为了阻止它，我们可以使用hint full,让t1不走索引了，这样就无法传值进来了。
merge into (select arc_receipt_detail_id,contract_no,dossier_count_t,t.paylist_code
from arc_mgr_detail t where t.id in (select /*+ FULL(t1) */t1.id
from arc_mgr_detail t1, arc_apply_detail d1
where t1.is_del = 0
and d1.is_del = 0
and t1.contract_no = d1.contract_no
and t1.paylist_code = d1.paylist_code
and t1.arc_receipt_detail_id = d1.arc_receipt_detail_id
and t1.dossier_count_t = 0
and d1.check_count > 0)
) t3
using (select max(d2.check_count) MCOUNT ,D2.contract_no,D2.arc_receipt_detail_id,D2.paylist_code
from arc_apply_detail d2, arc_mgr_detail t2
where t2.is_del = 0
and d2.is_del = 0
and t2.contract_no = d2.contract_no
and t2.paylist_code = d2.paylist_code
and t2.arc_receipt_detail_id = d2.arc_receipt_detail_id
and t2.dossier_count_t = 0
and d2.check_count > 0
group by D2.contract_no,D2.arc_receipt_detail_id,D2.paylist_code) d3
on (t3.contract_no = d3.contract_no and t3.arc_receipt_detail_id = d3.arc_receipt_detail_id
and t3.paylist_code = d3.paylist_code)
when matched then
update set t3.dossier_count_t =d3.MCOUNT;
执行计划如下：
嗯 999没了，传值也没了，无需二次计算了，让我们看看结果：
结果很不错。当场解决。
快刀斩乱麻，什么表结构，索引选择性，老司机不用看这么细。速度解决问题才是王道，嘿嘿。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
