CNDBA社区
概念
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
版权声明：本文为博主原创文章，未经博主允许不得转载。
