oracle分区表exchange原理
oracle分区表exchange原理
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本身并没有改变,和物理存储位置无关,但必须是分区表和非分区表 之间才可以交换。