12c Create Table for Exchange Partition

以前版本中如果我们要对一个分区表做EXCHANGE PARTITION时，都会通过create table as select * XXX来新建一张普通表用于exchange，但是可能遇到这样的情况，源表的部分字段被禁用或不可见，则会导致exchange的时候失败，原因是因为两个表的表结构不一致。

而在新的12.2版本中，提供了一种新的建表方法，通过FOR EXCHANGE WITH语句来确保做exchange分区操作时的表与分区表形状一致，当然这种建表的方法并不会同步建索引。

下面总结了CREATE TABLE FOR EXCHANGE WITH操作所带来的一些影响：

• 这个DDL操作主要是为了方便创建用于做exchange partition操作的表

• 这个操作其实就是对要做exchange表的克隆，包含字段的顺序以及属性

• 新建的表无法重命名，都是从源表继承下来

• 这个DDL操作中唯一一个可以改变的逻辑属性是表是否为partition

  比如源表为一个复合分区表，如果多个子分区交换到新表的分区下，这种情形时，你就可以指定新表是否需要为分区表。

  子分区与分区之间可以是非对称的，但是在建表的时候必须要精确匹配分区与子分区的对应关系。

• 可以指定的物理属性则为表使用的段属性

• 使用这个DDL操作可以复制的列包括不限于以下：不可用列、不可见列、虚拟列、函数索引表达式列以及其他内部设置和属性

以前版本CTAS

创建一个测试分区表作为源表，包含了几种特殊类型的字段

drop table t1 purge;

create table t1 (
id number,
col1 varchar2(100),
col2 varchar2(100),
col3 generated always as (id+100) virtual
)
partition by range (id)
(
partition p1 values less than (200),
partition p2 values less than (400)
);

create index idx_t1 on t1(id);

分别修改col1和col2的字段属性

xb@PDB12C> alter table t1 set unused(col1);

Table altered.

xb@PDB12C> alter table t1 modify col2 invisible;

Table altered.

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER                        >>>>====已经看不到col1和col2两个字段
    2      COL3                                     NUMBER

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T1';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
COL2                                     VARCHAR2                       100                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

创建一张新的普通表用来做EXCHANGE PARTITION

drop table t2 purge;
create table t2 as select * from t1 where 1=2;
create index idx_t2 on t2(id);

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22                      NO                   >>>>====新表字段并非虚拟列


insert into t2 
select level from dual connect by rownum>=200;

commit;

alter table t1
  exchange partition p1
  with table t2
  without validation
  update global indexes;

ERROR at line 3:
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

可以看到报错提示字段数量不一致，CTAS对于这种情况就不太适用。

新版本FOR EXCHANGE WITH

现在可以使用12.2新的语句CREATE TABLE ... FOR EXCHANGE WITH

drop table t2 purge;

create table t2 
    for exchange with table t1;
create index idx_t2 on t2(id);

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T2';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
COL2                                     VARCHAR2                       100                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

可以看到现在新建的T2表字段属性就跟T1完全一样，继续剩下的操作

insert into t2(id)
select level  from dual connect by rownum>=200;

commit;

xb@PDB12C> 
alter table t1
  exchange partition p1
  with table t2
  without validation
   update global indexes;

Table altered.

xb@PDB12C> select * from t1 partition (p1) where rownum>=10;

        ID       COL3
---------- ----------
         1        101
         2        102
         3        103
         4        104
         5        105
         6        106
         7        107
         8        108
         9        109
        10        110

10 rows selected.

则可以顺利完成分区交换操作。