8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION in Oracle Database 12c Release 1
The TRUNCATE [SUB]PARTITION
and EXCHANGE [SUB]PARTITION
commands can now include a CASCADE
clause, allowing the actions to cascade down the hierarchy of reference partitioned tables. For this to work, the referenced foreign keys must include the ON DELETE CASCADE
clause.
Related articles.
Setup
The following code creates a parent partitioned table (T1) with a child reference partitioned table (T2). Each partition is then populated with single row.
DROP TABLE t2 PURGE; DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_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, description VARCHAR2(50), created_date DATE, CONSTRAINT t2_pk PRIMARY KEY (id), CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE ) PARTITION BY REFERENCE (t2_t1_fk); INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY')); INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY')); INSERT INTO t2 VALUES (1, 1, 't2 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY')); INSERT INTO t2 VALUES (2, 2, 't2 TWO', TO_DATE('01/07/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>
TRUNCATE PARTITION ... CASCADE
Using the TRUNCATE PARTITION ... CASCADE
command, we can truncate the parent partition along with the referenced child partitions.
ALTER TABLE t1 TRUNCATE PARTITION part_2014 CASCADE 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>
EXCHANGE PARTITION ... CASCADE
To test the EXCHANGE PARTITION ... CASCADE
command, create the following non-partitioned tables. For the cascade to work, all the tables in the hierarchy from the exchange level down must be in place.
DROP TABLE t2_temp; DROP TABLE t1_temp; CREATE TABLE t1_temp ( id NUMBER, description VARCHAR2(50), created_date DATE, CONSTRAINT t1_temp_pk PRIMARY KEY (id) ); CREATE TABLE t2_temp ( id NUMBER NOT NULL, t1_id NUMBER NOT NULL, description VARCHAR2(50), created_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 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY')); INSERT INTO t2_temp VALUES (2, 2, 't2_temp TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY')); COMMIT;
The code below exchanges both the parent and child partitions, then checks the contents of the tables.
-- Exchange partitions. ALTER TABLE t1 EXCHANGE PARTITION part_2015 WITH TABLE t1_temp CASCADE UPDATE INDEXES; -- Check the data in the partitioned data. COLUMN t1_desc FORMAT A20 COLUMN t2_desc FORMAT A20 SELECT t1.description AS t1_desc, t2.description AS t2_desc FROM t1 JOIN t2 ON t2.t1_id = t1.id; T1_DESC T2_DESC -------------------- -------------------- t1_temp TWO t2_temp TWO SQL> -- Check the data in the temporary tables. COLUMN t1_temp_desc FORMAT A20 COLUMN t2_temp_desc FORMAT A20 SELECT t1_temp.description AS t1_temp_desc, t2_temp.description AS t2_temp_desc FROM t1_temp JOIN t2_temp ON t2_temp.t1_id = t1_temp.id; T1_TEMP_DESC T2_TEMP_DESC -------------------- -------------------- t1 TWO t2 TWO SQL>
For more information see:
- Truncating a Partition with the Cascade Option
- About Exchanging a Partition with the Cascade Option
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...