• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Create Interval-Reference Partitioned Tables in Oracle 12c

by admin

This is an example for the 12c new feature of creating interval partitioned tables as parent tables for reference partitioning.

1. Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.

CREATE TABLE parent (pk INT CONSTRAINT parent_pk PRIMARY KEY, i INT)
PARTITION BY RANGE(i) INTERVAL (10)
(partition p_p0 values less than ( 0),
partition p_p1 values less than (10),
partition p_p2 values less than (20),
partition p_p3 values less than (30));

Table created.
SQL> CREATE TABLE child(fk INT NOT NULL, i INT,
CONSTRAINT child_fk FOREIGN KEY(fk) REFERENCES parent(pk))
PARTITION BY REFERENCE(child_fk);

Table created.
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');

TABLE_NAME PARTITION_NAME       PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD      P_P0                                  1                      NO
CHILD      P_P1                                  2                      NO
CHILD      P_P2                                  3                      NO
CHILD      P_P3                                  4                      NO
PARENT     P_P0                                  1 0                    NO
PARENT     P_P1                                  2 10                   NO
PARENT     P_P2                                  3 20                   NO
PARENT     P_P3                                  4 30                   NO

8 rows selected.

2. At this moment there are no interval partitions present. After the following inserts, 2 interval partitions are created in the parent table and none in the child.

INSERT INTO parent VALUES(15, 15);
INSERT INTO parent VALUES(35, 35);
INSERT INTO parent VALUES(45, 45);
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');

TABLE_NAME PARTITION_NAME       PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD      P_P0                                  1                      NO
CHILD      P_P1                                  2                      NO
CHILD      P_P2                                  3                      NO
CHILD      P_P3                                  4                      NO
PARENT     P_P0                                  1 0                    NO
PARENT     P_P1                                  2 10                   NO
PARENT     P_P2                                  3 20                   NO
PARENT     P_P3                                  4 30                   NO
PARENT     SYS_P4543                             5 40                   YES
PARENT     SYS_P4544                             6 50                   YES

10 rows selected.

3. Some inserts will be made into the child table:

insert into child values (15,10);
insert into child values (35,11);
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');

TABLE_NAME PARTITION_NAME       PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD      P_P0                                  1                      NO
CHILD      P_P1                                  2                      NO
CHILD      P_P2                                  3                      NO
CHILD      P_P3                                  4                      NO
CHILD      SYS_P4543                             5                      YES
PARENT     P_P0                                  1 0                    NO
PARENT     P_P1                                  2 10                   NO
PARENT     P_P2                                  3 20                   NO
PARENT     P_P3                                  4 30                   NO
PARENT     SYS_P4543                             5 40                   YES
PARENT     SYS_P4544                             6 50                   YES

11 rows selected.

It can be seen that an interval partition was created in the child table as well, and as per the rule, the same name for the partition as per parent table was inherited.

4. Operations that transform interval partitions to conventional partitions in the parent table, such as ALTER TABLE SPLIT PARTITION on an interval partition, perform the corresponding transformation in the child table, creating partitions in the child table as necessary.

SQL> ALTER TABLE parent SPLIT PARTITION FOR (35) AT (35) INTO (partition px,partition py);

Table altered.
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');

TABLE_NAME PARTITION_NAME       PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD      P_P0                                  1                      NO
CHILD      P_P1                                  2                      NO
CHILD      P_P2                                  3                      NO
CHILD      P_P3                                  4                      NO
CHILD      PX                                    5                      NO
CHILD      PY                                    6                      NO
PARENT     P_P0                                  1 0                    NO
PARENT     P_P1                                  2 10                   NO
PARENT     P_P2                                  3 20                   NO
PARENT     P_P3                                  4 30                   NO
PARENT     PX                                    5 35                   NO
PARENT     PY                                    6 40                   NO
PARENT     SYS_P4544                             7 50                   YES

13 rows selected.

5. The SQL ALTER TABLE SET INTERVAL statement is not allowed for reference-partitioned tables, but can be run on tables that have reference-partitioned children. For example:

SQL> SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI') ;

TABLE_NAME PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- ------------------ -------------------- ---------
CHI                         1                      NO
CHI                         2                      YES
PAR                         1 10                   NO
PAR                         2 20                   YES
PAR                         3 30                   YES
PAR                         4 40                   YES

6 rows selected.
SQL> ALTER TABLE CHI SET INTERVAL (20);
ALTER TABLE CHI SET INTERVAL (20)
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.

6. In particular, ALTER TABLE SET INTERVAL removes the interval property from the targeted table and converts any interval-reference children to ordinary reference-partitioned tables.

SQL>ALTER TABLE par SET INTERVAL (20);

Table altered.
SQL> SELECT table_name, partition_position, high_value, interval FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI');

TABLE_NAME PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- ------------------ -------------------- ---------
CHI                         1                      NO
CHI                         2                      NO
PAR                         1 10                   NO
PAR                         2 20                   NO
PAR                         3 30                   NO
PAR                         4 40                   NO

6 rows selected.

7. After INSERT INTO par VALUES(45,45) is made a new interval partition can be created in parent table:

SQL> SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI');

TABLE_NAME PARTITION_POSITION HIGH_VALUE           INTERVAL
---------- ------------------ -------------------- ---------
CHI                         1                      NO
CHI                         2                      NO
PAR                         1 10                   NO
PAR                         2 20                   NO
PAR                         3 30                   NO
PAR                         4 40                   NO
PAR                         5 60                   YES

7 rows selected.

Also in a similar manner, the SQL ALTER TABLE SET STORE IN statement is not allowed for reference-partitioned tables but can be run on tables that have reference-partitioned children.

Note: Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. How to Drop/Truncate Multiple Partitions in Oracle 12C
  2. How to convert Linux dd .img to .VDI, VMDK, VHD with VIrtualBox Command
  3. Truncate Table Statement: REUSE STORAGE VS DROP STORAGE
  4. Oracle database 12c : How to Drop Partition(s)
  5. Oracle Database 12c New Feature – RMAN “RECOVER TABLE”
  6. How to Delete ASM Disk on Multipath Device in CentOS/RHEL
  7. New Connections to the Database lead to ORA-12518 or TNS-12518
  8. How to set udev rule for setting the disk permission on ASM disks when using multipath on CentOS/RHEL 6
  9. Oracle Database 12c New Feature – Move a Datafile Online
  10. Out-of-Place Refresh Option: Oracle 12c New Feature

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright