MERGE INTO 优化

[sbymdh2003]

代码如下：
MERGE INTO T_PA_CITEM_KIND t_pa
USING CCIC.PRPCITEMKIND t_p
ON (t_pa.POLICYNO = t_p.POLICYNO
AND t_pa.SEQNO = t_p.ITEMKINDNO )
WHEN MATCHED THEN
UPDATE
SET t_pa.RISKCODE = t_p.RISKCODE
, t_pa.CLAUSENAME = t_p.KINDNAME
, t_pa.KINDNAME = t_p.ItemDetailName
, t_pa.STARTDATE = t_p.STARTDATE
, t_pa.STARTHOUR = t_p.STARTHOUR
, t_pa.ENDDATE = t_p.ENDDATE
, t_pa.ENDHOUR = t_p.ENDHOUR
, t_pa.CURRENCY = t_p.CURRENCY
, t_pa.CALCULATEFLAG = t_p.CALCULATEFLAG
, t_pa.UNITAMOUNT = t_p.UNITAMOUNT
, t_pa.QUANTITY = t_p.QUANTITY
, t_pa.RATE = t_p.RATE
, t_pa.SHORTRATE = t_p.SHORTRATE
, t_pa.SHORTRATEFLAG = t_p.SHORTRATEFLAG
, t_pa.AMOUNT = t_p.AMOUNT
, t_pa.PREMIUM = t_p.PREMIUM
, t_pa.KINDVAT = t_p.KINDVAT
, t_pa.TNIPREMIUM = t_p.TNIPREMIUM
, t_pa.VATRATETYPE = t_p.VATRATETYPE
, t_pa.FLAG = t_p.FLAG
WHEN NOT MATCHED THEN
INSERT (POLICYNO, SEQNO, ITEMTYPE, REL_REF_SEQNO, RISKCODE, CLAUSECODE, CLAUSENAME, KINDCODE
, KINDNAME, STARTDATE, STARTHOUR, ENDDATE, ENDHOUR, CURRENCY, CALCULATEFLAG, UNITAMOUNT
, UNITPREMIUM, QUANTITY, RATE, SHORTRATE, SHORTRATEFLAG, AMOUNT, PREMIUM, KINDVAT
, TNIPREMIUM, VATRATETYPE, FLAG, ORIGININPUTFLAG)
VALUES( t_p.POLICYNO
, t_p.ITEMKINDNO
, NULL
, NULL
, t_p.RISKCODE
, null
, t_p.KINDNAME
, null
, t_p.ItemDetailName
, t_p.STARTDATE
, t_p.STARTHOUR
, t_p.ENDDATE
, t_p.ENDHOUR
, t_p.CURRENCY
, t_p.CALCULATEFLAG
, t_p.UNITAMOUNT
, NULL
, t_p.QUANTITY
, t_p.RATE
, t_p.SHORTRATE
, t_p.SHORTRATEFLAG
, t_p.AMOUNT
, t_p.PREMIUM
, t_p.KINDVAT
, t_p.TNIPREMIUM
, t_p.VATRATETYPE
, t_p.FLAG
, 'Y'
)
执行计划如下：


这两个表数据量都是4千万以上的。更新和插入的数据量都挺大的。实际执行过程还有一个时间标志，目前没有，所以没加。

执行时间太长，请问有什么优化的办法。两个都有主键，关联也是用主键关联的。
在线等，谢谢！

[minsic78]

如果你用的关联字段就是目标表的主键字段，那么可能有几个改进措施： 1、看看两表连接字段是否类型不一致？如果不一致，那么转化临时表的字段类型与目标表保持一致，要么修改表定义，要么使用对应函数修改临时表连接条件； 2、如果不是1的原因，那么可以收集两表统计信息试试：exec dbms_stats.gather_table_stats('用户名',‘表名',cascade=>true,method_opt=>'for all indexed columns repeat') 3、如果经过以上调整都速度很慢，无法调整执行计划中的HASH JOIN成为NESTED LOOPS，那么就添加提示，加在merge关键字后面：merge /*+use_nl_with_index(t_pa)*/ 4、如果经过3还不行，那么继续发添加提示后的语句和执行计划上来

[minsic78]

引用 1 楼 minsic78 的回复:

如果你用的关联字段就是目标表的主键字段，那么可能有几个改进措施： 1、看看两表连接字段是否类型不一致？如果不一致，那么转化临时表的字段类型与目标表保持一致，要么修改表定义，要么使用对应函数修改临时表连接条件； 2、如果不是1的原因，那么可以收集两表统计信息试试：exec dbms_stats.gather_table_stats('用户名',‘表名',cascade=>true,method_opt=>'for all indexed columns repeat') 3、如果经过以上调整都速度很慢，无法调整执行计划中的HASH JOIN成为NESTED LOOPS，那么就添加提示，加在merge关键字后面：merge /*+use_nl_with_index(t_pa)*/ 4、如果经过3还不行，那么继续发添加提示后的语句和执行计划上来

补充：如果你的临时表4千万数据全部参与merge，那么现在这个SQL慢是正常的，执行计划也可以说是合理的，但是如果实际跑的时候会有时间条件落到这张临时表上，过滤出少量记录，那么上贴发的建议才有实施的价值。

[卖水果的net]

两个 4000W 都 要计算吗 ？ 那最好分批更新，比如尝试原表每 10W 行一次。看看时间。

[「已注销」]

1.先确认update的数据量有多大？ 2.update的数据量多的话要检查T_PA_CITEM_KIND t_pa 表索引，一般都会把索引禁用后重新rebuilt，因为修改数据会产生大量的日志。 3.CCIC.PRPCITEMKIND t_p获取新增和修改的保单，并不需要做全量保单数据同步

[桃花岛黄岛主]

兄弟，数据库要是都像你这么干，天河一号早晚也要跑死

这种千万级别的数据，肯定要做大事务分割，无论什么业务逻辑，量少的时间都要简单。量大的时候都不简单，就算是一个简单的SQL都可以完成的东西，也不能就一个简单的SQL就去做

[秦根荣]

请添加索引联合索引或者parallel解决试试

[wjmwaq]

1、t_pa.POLICYNO ， t_p.POLICYNO ， t_pa.SEQNO ， t_p.ITEMKINDNO 建立索引（数据类型要一致）
2、索引没用上的话可以指定使用索引。
3、marge Into 后增加 /*+parallel(t_pa,5) parallel(t_p,5) use_hash(t_pa,t_p)*/ 此语句为并发语句，你的cpu和内存要足够，否则可能会拖垮你的cpu，5可以自定义，相应减少。
3、如果整体表数据较多，分段执行，即10w一次执行，循环。
4、最后，merge Into 实际上也可使用以下方式进行。T_PA_CITEM_KIND 表上增加 iscurrent (默认为1，有效)，
先update T_PA_CITEM_KIND set iscurrent=0 where exists (select 1 from PRPCITEMKIND f where f...=t... and f....=t....)
在 insert into T_PA_CITEM_KIND(字段,iscurrent) (select 字段,1 from PRPCITEMKIND)
每次取iscurrent=1的，此为数据仓库的做法，可以保留原始数据。具体看你是用。

[Mricoo_周]

这种大事务处理肯定要分成N个小事务来处理，比如用游标每10000行提交一次的方法更新，而且资源允许的话可以加上并行。你语句并不复杂只是单单的表体量把事务搞复杂化了