Oracle 12c 新特性 --- 创建与分区表进行交换的表

概念

 

A new DDL command allows the creation of a table that exactly matches the shape of a partitioned table and is therefore eligible for a partition or subpartition exchange of a partitioned table. Note that indexes are not created as part of this command.
新的DDL命令允许创建与分区表的形状完全匹配的表，因此有资格获得分区表的分区或分区交换。注意，索引不是作为该命令的一部分创建的。

Creating a table that qualifies for a partition or subpartition exchange can be a tedious task for older tables that have had various structural changes and reorganizations. With this new DDL, the task becomes very simple and straightforward to implement. It also adds some implicit business context to such an operation, as compared to a CREATE TABLE AS SELECT command.

创建符合分区或子分区交换条件的表，对于具有各种结构更改和重组的旧表来说，是一项单调乏味的任务。有了这个新的DDL，这个任务就变得非常简单，并且很容易实现。它还将一些隐式业务上下文添加到这样的操作中，与CREATE TABLE AS SELECT命令相比。

实验

在ORACLE 12.2中，create table语句被新的“for exchange With”子句丰富了。这使我们能够创建一个交换表，它是已分区表的克隆，它是稍后交换表命令的目标
新的子句处理数据类型、数据规模和精度，而不是NULL约束、隐藏列、虚拟列、未使用的列等。
索引、约束(除了非空)没有被克隆。

1) 创建分区表并建本地索引，主键，插入数据
[leo@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 31 00:15:58 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn scott/tiger@pdbcndba
Connected.
SQL> CREATE TABLE EMP_PART ( 
   EMPNO    NUMBER(4,0)       NOT NULL
  ,ENAME    VARCHAR2(10 CHAR) NOT NULL
  ,JOB      VARCHAR2(9 CHAR)  NOT NULL
  ,MGR      NUMBER(4,0)说
  ,HIREDATE DATE              NOT NULL
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2,0)       NOT NULL
  ,CONSTRAINT chk_sal_comm_less_1000 CHECK (NVL(sal,0) + NVL(comm,0) < 10000)
  ,CONSTRAINT chk_empno CHECK (empno between 1000 and 9999)
) 
PARTITION BY LIST (deptno) (
   PARTITION p_accounting VALUES (10)
  ,PARTITION p_research   VALUES (20)
  ,PARTITION p_sales      VALUES (30)
  ,PARTITION p_default    VALUES (DEFAULT)
);

Table created.
--创建本地索引
SQL> CREATE INDEX emp_part_idx ON EMP_PART (hiredate)
   LOCAL (
      PARTITION p_accounting_hiredate_idx
     ,PARTITION p_research_hiredate_idx
     ,PARTITION p_sales_hiredate_idx
     ,PARTITION p_default_hiredate_idx
); 

Index created.

--增加主键
SQL> ALTER TABLE emp_part 
   ADD CONSTRAINT emp_part_pk
   PRIMARY KEY (empno); 

Table altered.

--插入数据
SQL> INSERT INTO emp_part (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
  FROM emp
 WHERE deptno != 20;  

9 rows created.

SQL> commit;

Commit complete.
2 ） 增加虚拟列，隐藏列，列不可用
SQL> ALTER TABLE emp_part
   ADD total_income GENERATED ALWAYS AS (NVL(SAL,0) + NVL(COMM,0));  2  

Table altered.

SQL> ALTER TABLE emp_part MODIFY deptno INVISIBLE;

Table altered.

SQL> ALTER TABLE emp_part SET UNUSED COLUMN job;

Table altered.

--查看表分区
SQL> col table_name for a10
SQL> col partition_name for a40
SQL> SELECT table_name,
     partition_name
FROM  user_tab_partitions where table_name='EMP_PART';  2    3  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
EMP_PART   P_ACCOUNTING
EMP_PART   P_DEFAULT
EMP_PART   P_RESEARCH
EMP_PART   P_SALES

如果我们想要使用一个exchange分区命令来加载department 20的数据，并使用一个现有的表来保存所有这些行，那么我们就必须确保使用的表的结构、索引、约束等都与分区表相同。create table命令中的新子句帮助我们解决这个问题(至少是一些问题)。
3） 创建交换表
SQL> CREATE TABLE emp_part_exchange FOR EXCHANGE WITH TABLE emp_part;

Table created.
--表中插入数据
SQL> INSERT INTO emp_part_exchange(EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO
 FROM emp
 WHERE deptno = 20;

5 rows created.

SQL> commit;

Commit complete.

--查看表，可以看到虚拟列，隐藏列也被复制过来
SQL> set line 300
SQL> col column_name for a50
SQL> col DATA_TYPE for a30
SQL> col null for a10
SQL> col HIDDEN for a20
SQL>  VIRTUAL for a20
SQL> SELECT column_name
      ,data_type || 
       CASE data_type
          WHEN 'VARCHAR2' THEN '(' || data_length || ')'
          WHEN 'NUMBER' THEN NULLIF('(' || data_precision || ',' || data_scale || ')','(,)')
          WHEN 'DATE' THEN NULL
       END AS data_type
      ,nullable
      ,hidden_column
      ,virtual_column
  FROM user_tab_cols
 WHERE table_name = 'EMP_PART_EXCHANGE'
 ORDER BY segment_column_id;

COLUMN_NAME					   DATA_TYPE			  N HID VIR
-------------------------------------------------- ------------------------------ - --- ---
EMPNO						   NUMBER(4,0)			  N NO	NO
ENAME						   VARCHAR2(20) 		  N NO	NO
SYS_C00003_17083100:17:15$	   VARCHAR2(18) 		  Y YES NO
MGR						       NUMBER(4,0)			  Y NO	NO
HIREDATE					   DATE 			      N NO	NO
SAL						       NUMBER(7,2)			  Y NO	NO
COMM						   NUMBER(7,2)			  Y NO	NO
DEPTNO						   NUMBER(2,0)			  N YES NO
TOTAL_INCOME				   NUMBER			      Y NO	YES

9 rows selected.

--可以看到约束没有被创建
SQL> col constraint_name for a20
SQL> col constraint_type for a10
SQL> col search_condition_vc for a30
SQL> SELECT constraint_name, constraint_type, search_condition_vc
  FROM user_constraints
 WHERE table_name = 'EMP_PART_EXCHANGE';

CONSTRAINT_NAME      CONSTRAINT SEARCH_CONDITION_VC
-------------------- ---------- ------------------------------
SYS_C007531	           C		"DEPTNO" IS NOT NULL
SYS_C007532	           C		"EMPNO" IS NOT NULL
SYS_C007533	           C		"ENAME" IS NOT NULL
SYS_C007534	           C		"HIREDATE" IS NOT NULL

SQL> SELECT index_name, index_type, partitioned
  FROM user_indexes
 WHERE table_name = 'EMP_PART_EXCHANGE';
no rows selected

4）如果我们做一个交换分区在这一点上我们会失败如果交换表非空
SQL> ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Error starting at line : 1 in command -
ALTER TABLE emp_part 
 EXCHANGE PARTITION p_research 
 WITH TABLE emp_part_exchange
Error report -
SQL Error: ORA-14118: CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION
14118. 00000 - "CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the
 ALTER TABLE EXCHANGE PARTITION statement have CHECK constraint
 defined on them.
*Action: Ensure that the two tables do not have CHECK constraint
 defined on any column

我们仍然需要手动创建约束交换表…
--分区交换
SQL> ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_sal_comm_less_1000_ep 
   CHECK (NVL(sal,0) + NVL(comm,0) < 10000);

Table altered.

SQL> ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_empno_ep 
   CHECK (empno between 1000 and 9999);

Table altered.

SQL> ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT emp_part_pk_ep
   PRIMARY KEY (empno);  2    3  

--Table altered.
在此之后，交换分区就可以工作了
SQL> ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange;   2    3  

Table altered.

--查看分区数据
SQL> SELECT table_name,
     partition_name
FROM  user_tab_partitions where table_name='EMP_PART';  2    3  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
EMP_PART   P_ACCOUNTING
EMP_PART   P_DEFAULT
EMP_PART   P_RESEARCH
EMP_PART   P_SALES

SQL> SELECT * FROM EMP_PART PARTITION(p_research);

     EMPNO ENAME		       MGR HIREDATE	       SAL	 COMM TOTAL_INCOME
---------- -------------------- ---------- ------------ ---------- ---------- ------------
      7369 SMITH		      7902 17-DEC-80	       800		       800
      7566 JONES		      7839 02-APR-81	      2975		      2975
      7788 SCOTT		      7566 19-APR-87	      3000		      3000
      7876 ADAMS		      7788 23-MAY-87	      1100		      1100
      7902 FORD 		      7566 03-DEC-81	      3000		      3000

--查看约束
SQL> col constraint_name for a20
SQL> col constraint_type for a10
SQL> col search_condition_vc for a30
SQL> SELECT constraint_name, constraint_type, search_condition_vc
  FROM user_constraints
 WHERE table_name = 'EMP_PART_EXCHANGE';  2    3  

CONSTRAINT_NAME      CONSTRAINT SEARCH_CONDITION_VC
-------------------- ---------- ------------------------------
SYS_C007531	     C		"DEPTNO" IS NOT NULL
SYS_C007532	     C		"EMPNO" IS NOT NULL
SYS_C007533	     C		"ENAME" IS NOT NULL
SYS_C007534	     C		"HIREDATE" IS NOT NULL
CHK_SAL_COMM_LESS_10 C		NVL(sal,0) + NVL(comm,0) < 100
00_EP				00

CHK_EMPNO_EP	     C		empno between 1000 and 9999
EMP_PART_PK_EP	     P

7 rows selected.

总结:
 与create table as select 表达式创建表相比， FOR EXCHANGE WITH clause of CREATE TABLE 更进一步，它可以创建与分区表的形状完全匹配的表，除了表索引、约束不被创建。

参考官方文档：

http://docs.oracle.com/database/122/VLDBG/maintenance-partition-tables-indexes.htm#VLDBG1118