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

JOIN t2_temp ON t2_temp.t1_id = t1_temp.id;

T1_TEMP_info T2_TEMP_info

-------------------- --------------------

t1 data t2 data

SQL>

Oracle12c分割槽新特性之TRUNCATEPARTITION和EXCHANGE PARTITION級聯功能

相關文章