DBA Hub

📋Steps in this guide1/3

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.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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>
2

TRUNCATE PARTITION ... CASCADE

Using the command, we can truncate the parent partition along with the referenced child partitions.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
3

EXCHANGE PARTITION ... CASCADE

To test the 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. The code below exchanges both the parent and child partitions, then checks the contents of the tables. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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;

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!