Oracle 12c中分区（Partition）新特性之TRUNCATEPARTITION和EXCHANGE PARTITION级联功能

TRUNCATE [SUB]PARTITION和EXCHANGE [SUB]PARTITION命令如今可以包括CASCADE子句，从而允许参照分区表向下级联这些操作。为确保该选项正常，相关外键也必须包括DELETE子句。1. 设置下面创建一个分区父表（T1）和一个参照的分区子表（T2）。每个分区被插入一行数据。DROP TABLE t2 PURGE;DROP TABLE t1 PURGE;CREATE TABLE t1 （ id NUMBER, info VARCHAR2（50）, crt_dateDATE, CONSTRAINT t1_pk PRIMARY KEY （id））PARTITION BY RANGE （crt_date）（PARTITION part_2014 VALUES LESS THAN（TO_DATE（"01/01/2015", "DD/MM/YYYY"）） TABLESPACE users, PARTITION part_2015 VALUES LESS THAN（TO_DATE（"01/01/2016", "DD/MM/YYYY"）） TABLESPACE users）;CREATE TABLE t2 （ id NUMBER NOT NULL, t1_id NUMBER NOT NULL, info VARCHAR2（50）, crt_date DATE, CONSTRAINT t2_pk PRIMARY KEY （id）, CONSTRAINTt2_t1_fk FOREIGN KEY （t1_id） REFERENCES t1 （id） ON DELETE CASCADE）PARTITION BY REFERENCE （t2_t1_fk）;INSERT INTO t1 VALUES （1, "t1 data",TO_DATE（"05/05/2014", "DD/MM/YYYY"））;INSERT INTO t1 VALUES （2, "t1 data",TO_DATE（"05/05/2015", "DD/MM/YYYY"））;INSERT INTO t2 VALUES （1, 1, "t2 data",TO_DATE（"05/05/2014", "DD/MM/YYYY"））;INSERT INTO t2 VALUES （2, 2, "t2 data",TO_DATE（"05/05/2015", "DD/MM/YYYY"））;COMMIT;EXEC DBMS_STATS.gather_table_stats（USER,"t1"）;EXEC DBMS_STATS.gather_table_stats（USER,"t2"）;SELECT table_name, partition_name, num_rowsFROM user_tab_partitionsORDER BY 1,2;TABLE_NAME PARTITION_NAME NUM_ROWS-------------------- ------------------------------T1 PART_2014 1T1 PART_2015 1T2 PART_2014 1T2 PART_2015 1SQL>2. TRUNCATE PARTITION ... CASCADE通过TRUNCATE PARTITION ... CASCADE命令，我们可以清空父表分区和子表分区。ALTER TABLE t1 TRUNCATE PARTITION part_2014CASCADE UPDATE INDEXES;EXEC DBMS_STATS.gather_table_stats（USER,"t1"）;EXEC DBMS_STATS.gather_table_stats（USER, "t2"）;SELECT table_name, partition_name, num_rowsFROM user_tab_partitionsORDER BY 1,2;TABLE_NAME PARTITION_NAME NUM_ROWS-------------------- ------------------------------T1 PART_2014 0T1 PART_2015 1T2 PART_2014 0T2 PART_2015 1SQL>3. EXCHANGE PARTITION ... CASCADE为了测试EXCHANGE PARTITION ...CASCADE命令，创建如下非分区表。为确保层级功能正常，从交换级别开始往下的所有表必须存在。DROP TABLE t2_temp;DROP TABLE t1_temp;CREATE TABLE t1_temp （ id NUMBER, info VARCHAR2（50）, crt_dateDATE, CONSTRAINT t1_temp_pk PRIMARY KEY （id））;CREATE TABLE t2_temp （ id NUMBER NOT NULL, t1_id NUMBER NOT NULL, info VARCHAR2（50）, crt_date DATE, CONSTRAINT t2_temp_pk PRIMARY KEY （id）, CONSTRAINT t2_temp_t1_temp_fk FOREIGN KEY （t1_id） REFERENCES t1_temp（id） ON DELETE CASCADE）;INSERT INTO t1_temp VALUES （2, "t1_temp data",TO_DATE（"05/05/2015", "DD/MM/YYYY"））;INSERT INTO t2_temp VALUES （2, 2, "t2_tempdata", TO_DATE（"05/05/2015", "DD/MM/YYYY"））;COMMIT;如下交换父表和子表分区后，检查表中数据。-- 交换分区ALTER TABLE t1 EXCHANGE PARTITION part_2015 WITHTABLE t1_temp CASCADE UPDATEINDEXES;-- 检查分区中的数据COLUMN t1_info FORMAT A20COLUMN t2_info FORMAT A20SELECT t1.info AS t1_info, t2.info AS t2_infoFROM t1 JOIN t2 ON t2.t1_id = t1.id;T1_info T2_info-------------------- --------------------t1_temp data t2_temp dataSQL>-- 检查临时表中的数据。COLUMN t1_temp_info FORMAT A20COLUMN t2_temp_info FORMAT A20SELECT t1_temp.info AS t1_temp_info, t2_temp.info AS t2_temp_infoFROM t1_temp JOIN t2_temp ON t2_temp.t1_id = t1_temp.id;T1_TEMP_info T2_TEMP_info-------------------- --------------------t1 data t2 dataSQL>更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址