Monday, 25 September 2017

Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION in Oracle Database 12c

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.

◉ 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>