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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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 Setup UDEV Rules For RAC OCR And Voting Devices on Partitions
  2. Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value
  3. TSPITR fails With RMAN-06553
  4. ORA-00257:Archiver Error, Connect Internal Only Until Freed
  5. How to Create a Physical Standby from ASM Primary
  6. CentOS / RHEL 7 : How to set udev rules for ASM on multipath disks
  7. How to change the SCAN IP address (SCAN VIP resources) in 11gR2 Grid (CRS) environment
  8. Oracle RAC instabilities due to firewall (netfilter/iptables) enabled on the cluster interconnect
  9. Interview Questions : Oracle 12c Multitenant Database Architecture
  10. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB

You May Also Like

Primary Sidebar

Recent Posts

  • How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  • “Connection reset by peer” – error while ssh into a CentOS/RHEL system with a specific user only
  • MySQL: how to figure out which session holds which table level or global read locks
  • Recommended Configuration of the MySQL Performance Schema
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary