oracle分区的exchange操作非常快，那原理是什么呢？下面我们来做个实验：

SQL> create table test (id number(3));

表已创建。

SQL> insert into test values (1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select object_id,data_object_id from user_objects s where s.object_name='TEST';

 OBJECT_ID DATA_OBJECT_ID

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

    180148         180148

SQL> create table test_part(id number(3)) partition by range(id)

  2      (partition p1 values less than (10),

  3       partition p2 values less than (20));

表已创建。

SQL> col object_name format a20

SQL> col subobject_name format a20

SQL> select object_name,s.subobject_name,object_id, data_object_id

  2    from user_objects s

  3   where s.object_name = 'TEST_PART';

 

OBJECT_NAME          SUBOBJECT_NAME        OBJECT_ID DATA_OBJECT_ID

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

TEST_PART            P1                       180150         180150

TEST_PART            P2                       180151         180151

TEST_PART                                     180149

SQL> select * from test;

        ID

----------

         1

SQL> select * from test_part;

未选定行

SQL> alter table test_part exchange partition p1 with table test;

表已更改。

SQL> select * from test;

未选定行

SQL> select * from test_part;

        ID

----------

         1

SQL> select * from test_part partition (p1);

        ID

----------

         1

SQL> select object_id,data_object_id from user_objects s where s.object_name='TEST';

 OBJECT_ID DATA_OBJECT_ID

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

    180148         180150

SQL> select object_name,s.subobject_name,object_id, data_object_id

  2    from user_objects s

  3   where s.object_name = 'TEST_PART';

OBJECT_NAME          SUBOBJECT_NAME    OBJECT_ID   DATA_OBJECT_ID

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

TEST_PART            P1                 180150         180148

TEST_PART            P2                 180151         180151

TEST_PART                               180149

     exchange只是在oracle的数据字典中修改了分区和表的结构。数据并未发生任何修改，因此速度很快。更详细一点就是只是把不同object_id 对应的data_object_id交换了一下，而segment本身并没有改变，和物理存储位置无关，但必须是分区表和非分区表 之间才可以交换。