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_rows
FROM? user_tab_partitions
ORDER BY 1,2;
TABLE_NAME? ? ? ? ? PARTITION_NAME? ? ? ? NUM_ROWS
-------------------- ------------------------------
T1? ? ? ? ? ? ? ? ? PART_2014? ? ? ? ? ? ? ? ? ? 1
T1? ? ? ? ? ? ? ? ? PART_2015? ? ? ? ? ? ? ? ? ? 1
T2? ? ? ? ? ? ? ? ? PART_2014? ? ? ? ? ? ? ? ? ? 1
T2? ? ? ? ? ? ? ? ? PART_2015? ? ? ? ? ? ? ? ? ? 1
SQL>
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_rows
FROM? user_tab_partitions
ORDER BY 1,2;
TABLE_NAME? ? ? ? ? PARTITION_NAME? ? ? ? NUM_ROWS
-------------------- ------------------------------
T1? ? ? ? ? ? ? ? ? PART_2014? ? ? ? ? ? ? ? ? ? 0
T1? ? ? ? ? ? ? ? ? PART_2015? ? ? ? ? ? ? ? ? ? 1
T2? ? ? ? ? ? ? ? ? PART_2014? ? ? ? ? ? ? ? ? ? 0
T2? ? ? ? ? ? ? ? ? PART_2015? ? ? ? ? ? ? ? ? ? 1
SQL>
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 A20
COLUMN t2_info FORMAT A20
SELECT t1.info AS t1_info,
? ? ? t2.info AS t2_info
FROM? t1
? ? ? JOIN t2 ON t2.t1_id = t1.id;
T1_info? ? ? ? ? ? ? T2_info
-------------------- --------------------
t1_temp data? ? ? ? ? t2_temp data
SQL>
-- 检查临时表中的数据。
COLUMN t1_temp_info FORMAT A20
COLUMN t2_temp_info FORMAT A20
SELECT t1_temp.info AS t1_temp_info,
? ? ? t2_temp.info AS t2_temp_info
FROM? t1_temp