merge语法简介和等价改写优化案例分析(一)

oracle merge详细介绍
之前就看过sql中merge的使用,merge是oracle9I新增的语法,主要是用于简化更新的执行效率,后面有具体改写的案例分析。

具体语法如下:
merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.name=b.name
when not matched then
insert values(b.id,d,name)

用tab02表来对tab01更新,tab01的所有记录行中如果有符合的数据行就执行update操作a.id=b.id的就对tab01的name更新为tab02的name,如果没有满足a.id=b.id的数据行就对tab01表执行insert操作,注意此时matched只能update,not matched也只能insert或者不操作。

需要注意的是update是不能对关联条件update的:

merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.id=b.id,a.name=b.name
when not matched then
insert values(b.id,d,name)

此时oracle无法对连接条件的column进行update set

上面提到简化更新的执行效率,到底是如何简化的,何种情况下可以改写为merge来简化,下面xiaoyu参考oracle的改写调优案例分享中的例子来做简单的分享:

SQL>drop table t_objects;
SQL>drop table t_tables;
SQL>create table t_objects as select * from dba_objects;
SQL>create table t_tables as select * from dba_tables;
SQL>alter table t_objects add tablespace_name varchar2(32);


现在需要把t_tables的tablespace_name同步到t_objects的tablespace_name中
update t_objects a
set a.tablespace_name=(select b.tablespace_name from t_tables b where
b.table_name=a.object_name and b.owner=a.owner)
where exists(select 1 from  t_tables b where
b.table_name=a.object_name and b.owner=a.owner)

update t_objects a
set a.tablespace_name=(select b.tablespace_name from t_tables b where
b.table_name=a.object_name and b.owner=a.owner)

注意很多开发人员喜欢用第二种SQL去更新,这样oracle会把t_objects表中的所有tablespace_name都更新掉
这个一定要特别注意,如下例子:
SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 awk
         2 awk
         3 afg

SQL> select * from t02;

        ID EADDR
---------- ----------
         1 beijing
         2 shanghai

SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id);

3 rows updated.

SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 beijing
         2 shanghai
         3

SQL> rollback;

rollback complete.

SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id)
     where exists(select 1 from t02 where t01.id=t02.id);

2 rows updated.

SQL> select * from t01;

        ID NAME
---------- --------------------------------
         1 beijing
         2 shanghai
         3 afg

回到原SQL案例中认真分析下,原SQL执行计划如下:

SQL> UPDATE t_objects a
SET a.tablespace_name=
  (SELECT b.tablespace_name
  FROM t_tables b
  WHERE b.table_name=a.object_name
  AND b.owner       =a.owner
  )
WHERE EXISTS
  (SELECT 1 FROM t_tables b WHERE b.table_name=a.object_name AND b.owner=a.owner
  );

3174 rows updated.

Plan hash value: 2130220419

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |      1 |        |      0 |00:00:03.64 |     327K|       |       |          |
|   1 |  UPDATE               | T_OBJECTS |      1 |        |      0 |00:00:03.64 |     327K|       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI|           |      1 |  76604 |   3174 |00:00:00.22 |    1352 |  1229K|  1229K| 1335K (0)|
|   3 |    VIEW               | VW_SQ_1   |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   4 |     TABLE ACCESS FULL | T_TABLES  |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   5 |    TABLE ACCESS FULL  | T_OBJECTS |      1 |  76604 |  86958 |00:00:00.01 |    1245 |       |       |          |
|*  6 |   TABLE ACCESS FULL   | T_TABLES  |   3015 |      1 |   3015 |00:00:03.02 |     322K|       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="A"."OBJECT_NAME" AND "ITEM_2"="A"."OWNER")
   6 - filter(("B"."TABLE_NAME"=:B1 AND "B"."OWNER"=:B2)

Statistics
----------------------------------------------------------
          0  recursive calls
       3238  db block gets
     323957  consistent gets
          0  physical reads
     754468  redo size
        841  bytes sent via SQL*Net to client
       1014  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3174  rows processed

这个SQL的执行计划在于需要对表T_OBJECTS扫描两次,并且还有标量子查询部分(可以参考上面的T_TABLES表的循环查询了3015次)

而对于这类SQL最好改写为merge来优化:

merge into t_objects a
using t_tables b on(a.owner=b.owner and a.object_name=b.table_name)
when matched then update
set a.tablespace_name=b.tablespace_name;

Plan hash value: 1970127410

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |           |      1 |        |      0 |00:00:00.51 |    4590 |       |       |          |
|   1 |  MERGE               | T_OBJECTS |      1 |        |      0 |00:00:00.51 |    4590 |       |       |          |
|   2 |   VIEW               |           |      1 |        |   3174 |00:00:00.03 |    1352 |       |       |          |
|*  3 |    HASH JOIN         |           |      1 |   3915 |   3174 |00:00:00.03 |    1352 |  1598K|   984K| 2440K (0)|
|   4 |     TABLE ACCESS FULL| T_TABLES  |      1 |   2989 |   2959 |00:00:00.01 |     107 |       |       |          |
|   5 |     TABLE ACCESS FULL| T_OBJECTS |      1 |  76604 |  86958 |00:00:00.02 |    1245 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")

Statistics
----------------------------------------------------------
         66  recursive calls
       3238  db block gets
       1714  consistent gets
          0  physical reads
     754388  redo size
        844  bytes sent via SQL*Net to client
        947  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3174  rows processed

从执行计划来看merge改写的SQL已经没有标量子查询部分,逻辑读降低到了1714多

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in oracle, sql tuning and troubleshooting. Bookmark the permalink.