分区中的exchange

参考：http://www.orafaq.com/node/2570
 
网上说，分区交换比常规插入速度要快，照着做了一遍，确实比较快，但快的原因没搞清楚（附二），说是交换时只是交换了定义，所以快——这个先放着 （TOM说的，we just rename segments - the partition becomes a table, the table becomes the partition - no data is moved. 参考）https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1238800184155

  
以下是实验内容

A>set timing on
 
--创建测试表

A>create table t as select object_id id,object_name from all_objects;

Table created.
 
--经过多次插入后，查看表T的大小

A>select owner||'.'||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner='A';

OBJECT_NAME                  MB
 -------------------- ----------
 A.T                          63

A>create index t_pk on t(id) nologging;

Index created.
 
--因为我之前插入时，使用的是insert ... select方式，所以导致表T的id列有重复值，出现我竟然没注意，直到后面分区交换出现错误，我才想起来！@#￥%……&*（）

A>alter table t add constraint t_pk primary key (id);
 alter table t add constraint t_pk primary key (id)
                              *
 ERROR at line 1:
 ORA-02437: cannot validate (A.T_PK) - primary key violated
 
--创建分区表T_PART及本地索引

A>create table t_part(id number,object_name varchar2(30))
   2  partition by range(id)
   3  (
   4  partition p1 values less than(maxvalue)
   5  );

Table created.

A>create index t_part_pk on t_part(id) nologging local;

Index created.

A>alter table t_part add constraint t_part_pk primary key(id);

Table altered.

 --这个报错就是因为需要交换的两个表，索引不一致导致的，查看oracle错误代码就会知道，错误有两种可能：一是A表有索引B表没有，二是A,B表都有索引但不一致，我这应该属于第一种

A>alter table t_part exchange partition p1 with table t including indexes without validation;
 alter table t_part exchange partition p1 with table t including indexes without validation
 *
 ERROR at line 1:
 ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
 

--经过处理，表T中主键约束添加成功
 A>alter table t add constraint t_pk primary key (id);

Table altered.

以下是两种方式的对比

--分区交换只用了2.44秒

A>alter table t_part exchange partition p1 with table t including indexes
   2  without validation;

Table altered.

Elapsed: 00:00:02.44
 

--常规创建用了10.97秒

A>create table t_con as select * from t;

Table created.

Elapsed: 00:00:10.97
 

--交换前用户A各种对象的大小

A>select owner||'.'||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner='A' order by 1;

OBJECT_NAME                  MB
 -------------------- ----------
 A.T                          63
 A.T_CON                      64
 A.T_PART                     .1
 A.T_PART_PK                  .1
 A.T_PK                       54
 

--交换后表T及其索引都被交换到了分区表及分区表对应的索引上了

A>select owner||'.'||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner='A' order by 1;

OBJECT_NAME                  MB
 -------------------- ----------
 A.T                          .1
 A.T_CON                      64
 A.T_PART                     63
 A.T_PART_PK                  54
 A.T_PK                       .1
 

--经过比较，发现分区交换比常规创建方式快了近5倍！

A>select 10.97/2.44 from dual;

10.97/2.44
 ----------
 4.49590164
 
附一
 1.比较诡异的是，虽然我添加主键约束失败了，但查看用户A对象时，却发现T_PK赫然在列！
 

A>alter table t add constraint t_pk primary key (id);
 alter table t add constraint t_pk primary key (id)
                              *
 ERROR at line 1:
 ORA-02437: cannot validate (A.T_PK) - primary key violated

 Elapsed: 00:00:16.08
 A>select owner||'.'||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner='A';

OBJECT_NAME                  MB
 -------------------- ----------
 A.T                          63
 A.T_PK                       29
 
2.关于错误14130的解释
 A>!oerr ora 14130
 14130, 00000, "UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION"
 // *Cause:  One of the tables named in the ALTER TABLE EXCHANGE PARTITION
 //          command has a UNIQUE constraint for which no matching (vis-a-vis
//          key columns) constraint is defined on the other table or
//          a matching constraint is defined on the other table, but it
//          differs from that defined on the first table vis-a-vis being
//          enabled and/or validated.
 // *Action: Ensure that for every UNIQUE constraint defined on one of the
 //          tables named in the ALTER TABLE EXCHANGE PARTITION statement there
//          is a matching (vis-a-vis key columns and being enabled and/or
 //          validated) UNIQUE constraint defined on the other table.
 //          If UNIQUE constrains are enabled, UNIQUE constraints on the
//          partitioned table should be enforced using local indexes

 

附二
 A是原表，B是分区表

--这是交换前的信息

A>select SEGMENT_NAME,HEADER_BLOCK from dba_segments where owner='A';

SEGMENT_NAME    HEADER_BLOCK
--------------- ------------
A                        258
B                        266

A>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where OWNER='A' order by 1;

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------- -------------------
A                    59514          59516 TABLE
B                    59517                TABLE
B                    59518          59518 TABLE PARTITION

--这是交换后的信息

A>select SEGMENT_NAME,HEADER_BLOCK from dba_segments where owner='A';

SEGMENT_NAME    HEADER_BLOCK
--------------- ------------
A                        266
B                        258

A>select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where OWNER='A' order by 1;

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------- -------------------
A                    59514          59518 TABLE
B                    59517                TABLE
B                    59518          59516 TABLE PARTITION

可以看到，在交换前后，两个表的HEADER_BLOCK和DATA_OBJECT_ID进行了交换
也可以理解为，交换后，oracle将块258的段名由A改成了B，块266依此类推——或许这就是TOM说的我们只是改了段名，没有任何数据上的移动/复制

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29646619/viewspace-1164472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29646619/viewspace-1164472/