DBA Hub

📋Steps in this guide1/2

Interval-Reference Partitioning in Oracle Database 12c Release 1

Oracle 12c allows you to use an interval partitioned table as the parent for a reference partitioned table.

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

Overview

In previous releases you were able to do reference partitioning and interval partitioning, but you couldn't use an interval partitioned table as the parent for a reference partitioned table. Oracle 12c lifts that restriction, so you can now use interval-reference partitioning. - Interval-Reference Partitioning Related articles. The following code creates an interval partitioned table (T1) with yearly partitions and an interval-reference partitioned table (T2). As we insert data for each year, we can see the partitions are created for both tables.

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
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)
INTERVAL (NUMTOYMINTERVAL(12,'MONTH'))
(PARTITION part_01 VALUES LESS THAN (TO_DATE('01/01/2015', '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);
2

Section 2

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
-- Insert rows to 2014.
INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
INSERT INTO t2 VALUES (1, 1, 't2 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
COMMIT;

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT table_name,
       partition_name
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME
-------------------- --------------------
T1                   PART_01
T2                   PART_01

SQL>


-- Insert rows to 2015.
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
INSERT INTO t2 VALUES (2, 2, 't2 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
COMMIT;

SELECT table_name,
       partition_name
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME
-------------------- --------------------
T1                   PART_01
T1                   SYS_P835
T2                   PART_01
T2                   SYS_P835

SQL>


-- Insert rows to 2016.
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
INSERT INTO t2 VALUES (3, 3, 't2 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
COMMIT;

SELECT table_name,
       partition_name
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME
-------------------- --------------------
T1                   PART_01
T1                   SYS_P835
T1                   SYS_P836
T2                   PART_01
T2                   SYS_P835
T2                   SYS_P836

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!