oracle update 改为 merge

有个同学问过不看plan能不能优化，有些语句确实不需要看plan，今天就有个很简单的案例

比如下面这个：

网友说更新时间太长，需要1个小时

[sql] view plaincopyprint?

1. UPDATE ia
2. SET ia.col_time =
3. (SELECT MAX(substr(numtodsinterval(iaf.col_time, 'second'), 12, 8))
4. FROM iaf
5. WHERE ia.id = iaf.code
6. GROUP BY ia.id)
7. WHERE EXISTS (SELECT 1
8. FROM iaf
9. WHERE ia.id = iaf.code
10. GROUP BY ia.id);

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语句，改这种语句用不了几分钟

[sql] view plaincopyprint?

1. MERGE INTO ia
2. USING (SELECT iaf.code,
3. MAX(substr(numtodsinterval(iaf.col_time, 'second'), 12, 8)) col_time
4. FROM iaf
5. WHERE ia.id = iaf.code) iaf
6. USING (ia.id = iaf.code)
7. WHEN MATCHED THEN
8. UPDATE SET ia.col_time = iaf.col_time;

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的访问减少了一次。

 

http://blog.csdn.net/jgmydsai/article/details/37697749