oracle update 改为 merge
有个同学问过不看plan能不能优化,有些语句确实不需要看plan,今天就有个很简单的案例
比如下面这个:
网友说更新时间太长,需要1个小时
- UPDATE ia
- SET ia.col_time =
- (SELECT MAX(substr(numtodsinterval(iaf.col_time, 'second'), 12, 8))
- FROM iaf
- WHERE ia.id = iaf.code
- GROUP BY ia.id)
- WHERE EXISTS (SELECT 1
- FROM iaf
- WHERE ia.id = iaf.code
- GROUP BY ia.id);
象这种关联更新一般都可以改为merge语句,改这种语句用不了几分钟
- MERGE INTO ia
- USING (SELECT iaf.code,
- MAX(substr(numtodsinterval(iaf.col_time, 'second'), 12, 8)) col_time
- FROM iaf
- WHERE ia.id = iaf.code) iaf
- USING (ia.id = iaf.code)
- WHEN MATCHED THEN
- UPDATE SET ia.col_time = iaf.col_time;
单单对比这两个语句的写法就可以看到:改为merge后对于iaf的访问减少了一次。