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

概念https://www.cndba.cn/leo1990/article/2202https://www.cndba.cn/leo1990/article/2202

https://www.cndba.cn/leo1990/article/2202https://www.cndba.cn/leo1990/article/2202

https://www.cndba.cn/leo1990/article/2202https://www.cndba.cn/leo1990/article/2202

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命令相比。

实验 https://www.cndba.cn/leo1990/article/2202

https://www.cndba.cn/leo1990/article/2202

在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 NONO

ENAME VARCHAR2(20) N NONO

SYS_C00003_17083100:17:15$ VARCHAR2(18) Y YES NO

MGR NUMBER(4,0) Y NONO

HIREDATE DATE N NONO

SAL NUMBER(7,2) Y NONO

COMM NUMBER(7,2) Y NONO

DEPTNO NUMBER(2,0) N YES NO

TOTAL_INCOME NUMBER Y NOYES

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 CNVL(sal,0) + NVL(comm,0) < 100

00_EP00

CHK_EMPNO_EP Cempno between 1000 and 9999

EMP_PART_PK_EP P

7 rows selected.

总结:

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

https://www.cndba.cn/leo1990/article/2202

参考官方文档：

https://www.cndba.cn/leo1990/article/2202

版权声明：本文为博主原创文章，未经博主允许不得转载。