分区表交换分区

学习记录（原）

分区表交换分区
作用：能够将一个分区转换到一个非分区表或者一个非分区表转换为一个分区，交换分区在你想转换一个非分区表称为一个分区表的时候非常有用。其实这个过程并不实际的进行数据移动，只是修改了数据字典和进行数据验证

讨论如下几个方面：
对主键唯一键的影响：
对普通索引的影响：
对LOCAL index 的影响：
对GLOBAL index的影响：

实验：
建立一个范围分区表
CREATE TABLE t_pe (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20));
建立一个普通索引在I列
create index t_pe_i_n on t_pe(i);
建立一个LOCAL索引
create index t_pe_i_l on t_pe(j) local;
建立一个global索引（GLOBAL索引没有LIST形式的）
create index t_pe_i_g on t_pe(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

建立一个普通表
create table t_pe_ex
(i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))

分别插入值
insert into t_pe
values(1,5,'a','A');
insert into t_pe
values(2,15,'b','B');
insert into t_pe_ex
values(3,16,'c','C');

1、这个时候我们的普通表中并没有索引，执行语句
 ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex ;
查看普通索引状态
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_i_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_I_N                       UNUSABLE
查看本地分区索引
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_L                       UNUSABLE P2
T_PE_I_L                       USABLE   P1
查看全局分区索引
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_G                       UNUSABLE PG2
T_PE_I_G                       UNUSABLE PG1
可以看到这里的只有本地分区索引非操作分区还有效其他均无效，这个时候只有rebuild一下了
alter index t_pe_i_g rebuild partition PG2;

2、如果我们带上了update indexes什么效果呢？
脚本
drop table t_pe ;
drop table t_pe_ex;
CREATE TABLE t_pe (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20));

create index t_pe_i_n on t_pe(i);
create index t_pe_i_l on t_pe(j) local;
create index t_pe_i_g on t_pe(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);
create table t_pe_ex
(i NUMBER, j NUMBER , f varchar2(20),k varchar2(20));
create index t_pe_ex_i_n on t_pe_ex(i);
create index t_pe_ex_i_n2 on t_pe_ex(j);
create index t_pe_ex_i_n3 on t_pe_ex(f);
insert into t_pe
values(2,5,'a','A');
insert into t_pe
values(1,15,'b','B');
insert into t_pe_ex
values(3,16,'c','C');
insert into t_pe_ex
values(2,15,'d','D');
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex update indexes;

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_i_n';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n2';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n3';

SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex update indexes;
 
Table altered
 
 
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_i_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_I_N                       VALID
 
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_L                       UNUSABLE P2
T_PE_I_L                       USABLE   P1
 
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_G                       USABLE   PG2
T_PE_I_G                       USABLE   PG1

可以看到普通索引是有效的但是本地分区索引任然无效。还是需要rebuild

如果普通表也包含了索引那全部失效

SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N                    UNUSABLE
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n2';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N2                   UNUSABLE
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n3';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N3                   UNUSABLE

3、测试一下如果包含了INCLUDING INDEXES，那我们就需要在我们的普通表中加入相同的索引，并且INCLUDING INDEXES只支持LOCAL index的交换，任何包含普通索引（包含你的主键索引唯一键索引，条件非常苛刻）和全局索引的交换也是

要报错ORA-14098
drop index t_pe_i_n
drop index  t_pe_i_g
create index t_pe_ex_i_n2 on t_pe_ex(j);
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_L                       USABLE   P1
T_PE_I_L                       USABLE   P2
 
SQL> select INDEX_NAME ,STATUS from dba_indexes a where lower(index_name)='t_pe_ex_i_n2';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N2                   VALID

SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex including indexes;
 
Table altered
 
SQL>
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_L                       USABLE   P1
T_PE_I_L                       USABLE   P2
 
SQL>
SQL> select INDEX_NAME ,STATUS from dba_indexes a where lower(index_name)='t_pe_ex_i_n2';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N2                   VALID
可以看到都是可用的，没有问题。为什么要不支持普通索引和全局索引，因为普通索引和全局索引和表的分区并不相同，所以没办法。

 

4、包含主键约束的测试
包含主键约束的表没办法是用including indexes，而且交换的表都必须在同样的列上拥有同样的主键约束，对于主键索引的失效只有是用rebuild，即使带上update index，也只能保证交换

表中不失效。
drop table t_pe ;
drop table t_pe_ex;
CREATE TABLE t_pe (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20));

alter table t_pe add constraint pk_t_pe primary key(i);
create table t_pe_ex
(i NUMBER, j NUMBER , f varchar2(20),k varchar2(20));
alter table t_pe_ex add constraint pk_t_pe_ex primary key(i);

insert into t_pe
values(1,5,'a','A');
insert into t_pe
values(2,15,'b','B');
insert into t_pe_ex
values(3,16,'c','C');
insert into t_pe_ex
values(2,15,'d','D');

select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe_ex';
select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe';
带上update index
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex update indexes;
 
Table altered
 
SQL>
SQL> select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe_ex';
 
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE PK_T_PE_EX
SQL> select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe';
 
STATUS   INDEX_NAME
-------- ------------------------------
VALID    PK_T_PE
不带update index
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex;
 
Table altered
 
SQL>
SQL> select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe_ex';
 
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE PK_T_PE_EX
SQL> select STATUS,INDEX_NAME from dba_indexes where lower(table_name)='t_pe';
 
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE PK_T_PE

可以看到即使带了update index 也只能保证我们的分区表的索引有效。

另外如果需要加速可以使用WITHOUT VALIDATION选项，但是如果有主键和唯一键，这个选项会忽略
The WITHOUT VALIDATION is optional, but makes it a lot quicker. It avoids the need for the system to check every partition key value to make sure the row is going into

the correct partition. Obviously it needs to be used with care.
但是如下可以逃避
Note:

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However,

if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation will be performed as

if WITH VALIDATION were specified in order to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:

ALTER TABLE table_name
     DISABLE CONSTRAINT constraint_name KEEP INDEX

Then, enable the constraints after the exchange.
如下：
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex WITHOUT VALIDATION;
 
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex WITHOUT VALIDATION
 
ORA-00001: 违反唯一约束条件 (SYS.PK_T_PE)
 
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex WITHOUT VALIDATION;
 
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex WITHOUT VALIDATION
 
ORA-00001: 违反唯一约束条件 (SYS.PK_T_PE)

 

结论：

1、对于普通索引只能支持update indexes或者不带任何选项，如果带上UPDATE INDEXES，那么分区表中可以自动重建，但是普通表中不行。如果不带则都失效。

including indexes不支持普通索引。

2、对于local索引支持update indexes和including indexes当然也可以不带任何选项，对于updae indexes实际没有太大的作用和不带一样，都会导致分区表中交换出分区都会失效，普通表中也是失效的。但是如果是INCLUDING INDEXES那么都不会失效，但这个要求未免太苛刻，这个表上只能有LOCAL indexes。

3、对于GLOBAL INDEX只能支持update indexes或者不带任何选项，带或者不带UPDATE INDEXES 普通表索引都会失效，但是带了UPDATE INDEXES 则会自动重建分区表的全局索引，不会失效。

4、对于主键，尚没有测试LOCAL INDEX类型的主键，但是对于一般的主键约束实际和普通索引一样。如果带上UPDATE INDEXES，那么分区表中可以自动重建，但是普通表中不行。如果不带则都失效。including indexes不支持普通索引。

 

最后测试下LOCAL INDEX类型的主键其实这个和结论2是一致的

SQL> CREATE TABLE t_pe (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
  2       PARTITION BY RANGE(j)
  3          (PARTITION p1 VALUES LESS THAN (10),
  4           PARTITION p2 VALUES LESS THAN (20));
 
Table created
 
SQL> create index t_pe_i_l on t_pe(j) local;
 
Index created
 
SQL> alter table t_pe add constraint pk_t_pe primary key(j);
 
Table altered
 
SQL>
SQL> create table t_pe_ex
  2  (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20));
 
Table created
 
SQL> create index t_pe_ex_i_n2 on t_pe_ex(j);
 
Index created
 
SQL> alter table t_pe_ex add constraint pk_t_pe_ex primary key(j);
 
Table altered

 

SQL> insert into t_pe
  2  values(2,5,'a','A');
 
1 row inserted
SQL> insert into t_pe
  2  values(1,15,'b','B');
 
1 row inserted
SQL> insert into t_pe_ex
  2  values(3,16,'c','C');
 
1 row inserted
SQL> insert into t_pe_ex
  2  values(3,18,'d','D');
 SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex  including indexes;
 
Table altered
 
SQL>
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_i_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_I_L                       USABLE   P2
T_PE_I_L                       USABLE   P1
 
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_ex_i_n2';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_EX_I_N2                   VALID

 

 

还需要注意非前缀的LOCAL INDEX不能作为主键的索引

SQL> CREATE TABLE t_pe (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
  2       PARTITION BY RANGE(j)
  3          (PARTITION p1 VALUES LESS THAN (10),
  4           PARTITION p2 VALUES LESS THAN (20));
 
Table created

 

SQL> create index t_pe_i_n on t_pe(i) local;
 
Index created
 
SQL>  alter table t_pe add constraint pk_t_pe primary key(i);
 
alter table t_pe add constraint pk_t_pe primary key(i)
 
ORA-01408: 此列列表已索引
  
SQL> create index t_pe_i_l on t_pe(j) local;
 
Index created
 
SQL>  alter table t_pe add constraint pk_t_pe primary key(j);
 
Table altered

 

-------------------------------
相关报错：
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex update indexes;
 
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex update indexes
 
ORA-14099: 未对指定分区限定表中的所有行
说明你的exchange表中有在PARTITION表中未定义的行，换句话说就是数据超过了你定义的上限。
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex including indexes;
 
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex including indexes
 
ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
表示你的普通表中的索引和分区表并不匹配，或者是你包含了普通或者全局索引。
SQL> ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex ;
 
ALTER TABLE t_pe EXCHANGE PARTITION p2 WITH TABLE t_pe_ex
 
ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配
这表示的你列类型在两边表不一致，包括你的约束，比如A表的I列有主键约束，B表的I列也必须要有。