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                      |           |       |       |            |          |       |       |