以前看到过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)
7where 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)
7where 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
10delete
11where (merge1.name = 'cat');
4 行已合并。
下面看看执行计划：
----第一次的执行计划
SQL> rollback
2;
回退已完成。
SQL> set autotrace on;
SQL> alter system flush shared_pool;
系统已更改。
SQL>insert intomerge1
2 select * from merge2
3 where not exists (select * frommerge1 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
统计信息
----------------------------------------------------------
1510recursive calls
3db block gets
261consistent gets
0physical reads
0redo size
675bytes sent via SQL*Net to client
657bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
34sorts (memory)
0sorts (disk)
1rows 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
统计信息
----------------------------------------------------------
605recursive calls
3db block gets
89consistent gets
0physical reads
0redo size
659bytes sent via SQL*Net to client
708bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
9sorts (memory)
0sorts (disk)
1rows 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
统计信息
----------------------------------------------------------
605recursive calls
3db block gets
89consistent gets
0physical reads
0redo size
659bytes sent via SQL*Net to client
708bytes received via SQL*Net from client
4SQL*Net roundtrips to/from client
9sorts (memory)
0sorts (disk)
1rows processed
---上面的执行计划结果说明merge into 比另一个写法更有效率？
HASH JOIN ANTI 和HASH JOIN OUTER有什么区别？
另外，我想再问下，clear buffer 和 flush shared_pool 的区别？
还有我想再执行计划中看到物理读的次数，要用什么命令清除才能看到物理读的次数？
