(轉)merge into 的用法

merge into 的用法

 以前看到过merge into 不过都没怎么留意过，今天看到了，赶紧记录下：

SQL> select version from product_component_version;

VERSION

------------------------------------------------------

10.2.0.1.0

10.2.0.1.0

10.2.0.1.0

10.2.0.1.0

SQL> create table merge1 (id number,name varchar2(30 char),partment varchar2(1

char));

表已创建。

SQL> create table merge2 (id number,name varchar2(30 char),partment varchar2(1

char));

表已创建。

SQL> insert into merge1 values(100,'tom','A');

已创建 1 行。

SQL> insert into merge1 values(101,'cat','A');

已创建 1 行。

SQL> insert into merge1 values(102,'dog','B');

已创建 1 行。

SQL> insert into merge1 values(103,'fish','C');

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into merge2 values(103,'fish','D');

已创建 1 行。

SQL> insert into merge2 values(102,'dog','B');

已创建 1 行。

SQL> insert into merge2 values(101,'cat','A');

已创建 1 行。

SQL> insert into merge2 values(108,'dog','B');

已创建 1 行。

SQL> commit;

----不带where子句

SQL>  merge into merge1

  2     using merge2

  3   on (merge1.id = merge2.id)

  4   when matched then

  5     update

  6    set merge1.name = merge2.name;

3 行已合并。

SQL> rollback;

回退已完成。

---带where子句

SQL>   merge into merge1 a

  2    using merge2 b

  3   on (a.id = b.id)

  4   when matched then

  5    update

  6    set a.name = b.name

  7   where a.name <> b.name;

0 行已合并。

------如果要更新b表，这样写会出错

SQL>    merge into merge1 a

  2     using merge2 b

  3    on (a.id = b.id)

  4    when matched then

  5     update

  6     set b.name = a.name;

   set b.name = a.name

       *

第 6 行出现错误:

ORA-00904: "B"."NAME": 标识符无效

这里有个疑问，假设merge1表有800W数据，而merge2表只有20W数据。我要更新merge2表的数据，保持和merge1表一致的话，难道只能把merge1表作为基表？

SQL>    merge into merge2 a

  2     using merge2 1

  3    on (a.id = b.id)

  4    when matched then

  5     update

  6     set a.name = b.name;

这样写，我感觉性能大打折扣，要对比800W次。这就是说merge into只有在大表的数据需要和小表的数据保持一致的情况下才更能更好的提升性能？而小表的数据需要和大表保持一致时，merge into 不是理想的选择？

SQL>  merge into merge1

  2    using merge2

  3   on (merge1.id = merge2.id)

  4   when not matched then

  5   insert

  6   values(merge2.id,merge2.name,merge2.partment)

  7  where name = 'dog';

 merge into merge1

*

第 1 行出现错误:

ORA-38102: INSERT WHERE 子句中的列无效: "MERGE1"."NAME"

where条件的列必须声明，否则回认为是merge1的列

SQL>  merge into merge1

  2    using merge2

  3   on (merge1.id = merge2.id)

  4   when not matched then

  5   insert

  6   values(merge2.id,merge2.name,merge2.partment)

  7  where merge2.name = 'dog';

1 行已合并。

SQL>  merge into merge1

  2    using merge2

  3   on (merge1.id = merge2.id)

  4   when not matched then

  5   insert

  6   values(merge2.id,merge2.name,merge2.partment)

  7   when matched then

  8   update

  9   set merge1.name = merge2.name,

 10     merge1.partment = merge2.partment;

4 行已合并。

SQL> rollback;

回退已完成。

SQL>  merge into merge1

  2    using merge2

  3   on (merge1.id = merge2.id)

  4   when not matched then

  5   insert

  6   values(merge2.id,merge2.name,merge2.partment)

  7   when matched then

  8   update

  9   set merge1.name = merge2.name

 10  delete

 11  where (merge1.name = 'cat');

4 行已合并。

下面看看执行计划：

----第一次的执行计划

SQL> rollback

  2  ;

回退已完成。

SQL> set autotrace on;

SQL> alter system flush shared_pool;

系统已更改。

SQL>  insert into  merge1

  2    select * from merge2

  3        where not exists (select * from  merge1 where merge1.id = merge2.id)

已创建 1 行。

执行计划

----------------------------------------------------------

Plan hash value: 3303303066

-----------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | INSERT STATEMENT   |        |     1 |    70 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN ANTI    |        |     1 |    70 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| MERGE2 |     4 |   228 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| MERGE1 |     5 |    65 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("MERGE1"."ID"="MERGE2"."ID")

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

       1510  recursive calls

          3  db block gets

        261  consistent gets

          0  physical reads

          0  redo size

        675  bytes sent via SQL*Net to client

        657  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

         34  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> rollback;

回退已完成。

----第二次的执行计划

SQL> alter system flush shared_pool;

系统已更改。

SQL>  merge into merge1

  2        using merge2

  3        on (merge1.id = merge2.id)

  4          when not matched then

  5          insert

  6         values(merge2.id,merge2.name,merge2.partment);

1 行已合并。

执行计划

----------------------------------------------------------

Plan hash value: 1212982789

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | MERGE STATEMENT      |        |     4 |   456 |     5  (20)| 00:00:01 |

|   1 |  MERGE               | MERGE1 |       |       |            |          |

|   2 |   VIEW               |        |       |       |            |          |

|*  3 |    HASH JOIN OUTER   |        |     4 |   504 |     5  (20)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| MERGE2 |     4 |   228 |     2   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| MERGE1 |     5 |   345 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("MERGE1"."ID"(+)="MERGE2"."ID")

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

        605  recursive calls

          3  db block gets

         89  consistent gets

          0  physical reads

          0  redo size

        659  bytes sent via SQL*Net to client

        708  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          9  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> rollback;

回退已完成。

SQL> alter system flush shared_pool;

系统已更改。

SQL>  merge into merge1

  2        using merge2

  3        on (merge1.id = merge2.id)

  4          when not matched then

  5          insert

  6         values(merge2.id,merge2.name,merge2.partment);

1 行已合并。

执行计划

----------------------------------------------------------

Plan hash value: 1212982789

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | MERGE STATEMENT      |        |     4 |   456 |     5  (20)| 00:00:01 |

|   1 |  MERGE               | MERGE1 |       |       |            |          |

|   2 |   VIEW               |        |       |       |            |          |

|*  3 |    HASH JOIN OUTER   |        |     4 |   504 |     5  (20)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| MERGE2 |     4 |   228 |     2   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| MERGE1 |     5 |   345 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("MERGE1"."ID"(+)="MERGE2"."ID")

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

        605  recursive calls

          3  db block gets

         89  consistent gets

          0  physical reads

          0  redo size

        659  bytes sent via SQL*Net to client

        708  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          9  sorts (memory)

          0  sorts (disk)

          1  rows processed

---上面的执行计划结果说明merge into 比另一个写法更有效率？

HASH JOIN ANTI 和  HASH JOIN OUTER有什么区别？

另外，我想再问下，clear buffer 和 flush shared_pool 的区别？

还有我想再执行计划中看到物理读的次数，要用什么命令清除才能看到物理读的次数？