1、构造测试数据
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>create table t_local01 partition by range(object_id)
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(40000),
partition p5 values less than(maxvalue))
as select * from dba_objects;
SQL>create table t_local02 as select distinct data_object_id from dba_Objects;
SQL>alter table t_local01 enable row movement;
SQL>insert into t_local01 select * from t_local01 where object_id<10000;
SQL>commit;
SQL>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_LOCAL01');
SQL>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_LOCAL02');
2、merge into SQL语句的where条件位置不影响SQL执行结果
SQL> merge into (select * from t_local01 where object_id>10000)a
using t_local02 b
on (a.data_object_id=b.data_object_id)
when matched then
update set a.object_id=10000;
6296 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 908326285
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 7741 | 740K| 331 (1)| 00:00:04 | | |
| 1 | MERGE | T_LOCAL01 | | | | | | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 7741 | 778K| 331 (1)| 00:00:04 | | |
| 4 | TABLE ACCESS FULL | T_LOCAL02 | 8694 | 43470 | 6 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR| | 76629 | 7333K| 325 (1)| 00:00:04 | 2 | 5 |
|* 6 | TABLE ACCESS FULL | T_LOCAL01 | 76629 | 7333K| 325 (1)| 00:00:04 | 2 | 5 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_LOCAL01"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")
6 - filter("OBJECT_ID">10000)
Statistics
----------------------------------------------------------
53 recursive calls
18847 db block gets
7237 consistent gets
0 physical reads
6062672 redo size
845 bytes sent via SQL*Net to client
957 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
6296 rows processed
SQL> rollback;
Rollback complete.
SQL> merge into t_local01 a
using t_local02 b
on (a.data_object_id=b.data_object_id)
when matched then
update set a.object_id=10000
where a.object_id>10000
;
6296 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 539303167
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 8732 | 946K| 2524 (1)| 00:00:31 | | |
| 1 | MERGE | T_LOCAL01 | | | | | | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 8732 | 878K| 2524 (1)| 00:00:31 | | |
| 4 | TABLE ACCESS FULL | T_LOCAL02 | 8694 | 43470 | 6 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL| | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
| 6 | TABLE ACCESS FULL | T_LOCAL01 | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")
Statistics
----------------------------------------------------------
2 recursive calls
18897 db block gets
15238 consistent gets
0 physical reads
6064348 redo size
845 bytes sent via SQL*Net to client
961 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6296 rows processed
上面两个SQL语句的where条件a.object_id>10000确实生效了，如果不带入where a.object_id>10000条件，跟加了where条件是完全不一致的。
SQL>merge into t_local01 a
using t_local02 b
on (a.data_object_id=b.data_object_id)
when matched then
update set a.object_id=10000
;
8732 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 539303167
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 8732 | 835K| 2524 (1)| 00:00:31 | | |
| 1 | MERGE | T_LOCAL01 | | | | | | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 8732 | 878K| 2524 (1)| 00:00:31 | | |
| 4 | TABLE ACCESS FULL | T_LOCAL02 | 8694 | 43470 | 6 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL| | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
| 6 | TABLE ACCESS FULL | T_LOCAL01 | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
26545 db block gets
20173 consistent gets
0 physical reads
8515452 redo size
846 bytes sent via SQL*Net to client
928 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8732 rows processed
3、merge into SQL语句的where条件位置可能会影响SQL执行计划，主要是主表是否可以选择索引、分区裁剪之类的(建议是写在表改造的SQL语句中，如果写在ON条件需要注意ON条件比对不能和update条件重复）
SQL> create index ind_t_local01 on t_local01(object_id);
Index created.
SQL>merge into t_local01 a
using t_local02 b
on (a.data_object_id=b.data_object_id)
when matched then
update set a.object_id=10000
where a.object_id<100;
97 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 539303167
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 8732 | 946K| 2524 (1)| 00:00:31 | | |
| 1 | MERGE | T_LOCAL01 | | | | | | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 8732 | 878K| 2524 (1)| 00:00:31 | | |
| 4 | TABLE ACCESS FULL | T_LOCAL02 | 8694 | 43470 | 6 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL| | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
| 6 | TABLE ACCESS FULL | T_LOCAL01 | 86444 | 8272K| 2518 (1)| 00:00:31 | 1 | 5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
697 db block gets
9530 consistent gets
0 physical reads
137912 redo size
846 bytes sent via SQL*Net to client
949 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
97 rows processed
SQL> merge into (select * from t_local01 where object_id<100)a
using t_local02 b
on (a.data_object_id=b.data_object_id)
when matched then
update set a.object_id=10000;
97 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 3807083472
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 24 | 2352 | 10 (0)| 00:00:01 | | |
| 1 | MERGE | T_LOCAL01 | | | | | | |
| 2 | VIEW | | | | | | | |
|* 3 | HASH JOIN | | 24 | 2232 | 10 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_LOCAL01 | 96 | 8448 | 4 (0)| 00:00:01 | 1 | 1 |
|* 5 | INDEX RANGE SCAN | IND_T_LOCAL01 | 96 | | 2 (0)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | T_LOCAL02 | 8694 | 43470 | 6 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_LOCAL01"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID")
5 - access("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
16 recursive calls
717 db block gets
162 consistent gets
0 physical reads
156244 redo size
846 bytes sent via SQL*Net to client
956 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
97 rows processed
SQL> merge into t_local01 a
using t_local02 b
on (a.data_object_id=b.data_object_id and a.object_id<100)
when matched then
update set a.object_id=10000;
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."OBJECT_ID"
[oracle@ora121c-node01 ~]$ oerr ora 38104
38104, 00000, "Columns referenced in the ON Clause cannot be updated: %s"
// *Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause
// *Action:
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
