In this post I will show how to create Interval, Reference and System partitioned tables.
Creating Interval partitioned table
References:
11g New Features:INTERVAL PARTITIONING (Doc ID 805976.1)
11g Feature: Interval Partitioning Example (Doc ID 466352.1)
Interval Partitioning By Week (Doc ID 757754.1)
11g Partitioning Enhancements (Doc ID 452447.1)
Interval Partitioning Essentials – Common Questions – Top Issues (Doc ID 1479115.1)
Interval Partitioning By Week (Doc ID 757754.1)
Interval partitioning is an extension to the range partitioning where new partitions are created automatically when the new data doesn’t fit any available partition. Instead of getting “ORA-14400: inserted partition key does not map to any partition” error, a new partition
is created based on an interval criterion.
It is not possible to create an interval partition from OEM. To create it from a command line, refer to the main partitioning documentation that was provided above and go to “Creating Interval-Partitioned Tables” section.
Below, we will create an interval partitioned table with one partition, and the next partitions will be created automatically: SQL> CREATE TABLE tbl_interval_part ( id NUMBER, name VARCHAR2 (20), surname VARCHAR (20), ddate DATE ) PARTITION BY RANGE (ddate) INTERVAL ( NUMTOYMINTERVAL (1, ‘MONTH’) ) (PARTITION part1 VALUES LESS THAN (TO_DATE (‘05052016’, ‘ddmmyyyy’))); Table created. SQL>
Now query DBA_TAB_PARTITIONS to get the list of all partitions and insert one row where the value of the date column is less than the value of the partition part1.
SQL> COL partition_name FORMAT a20 COL high_value FORMAT a50 WORD_WRAPPED SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = ‘TBL_INTERVAL_PART’ ORDER BY partition_position; PARTITION_NAME HIGH_VALUE -------------------- -------------------------------------------------- PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SQL> INSERT INTO tbl_interval_part VALUES (1,’first’,’row’,TO_DATE (‘01052016’, ‘ddmmyyyy’)); 1 row created.
Now insert the second row with the date value greater than the partition value:
SQL> INSERT INTO tbl_interval_part VALUES (2,’second’,’row’,TO_DATE (‘03062016’, ‘ddmmyyyy’)); 1 row created.
A new partition with “interval one month” will be created automatically.
Here’s what we have so far:
– 05-05-2016 is the partition with the highest value
– Interval is one month
– Inserted row is 03-06-2016. It means that new partition will be created with the highest value 05-06-2016 (05-05-2016+1 month).
Let’s query DBA_TAB_PARTITIONS view to get information about the new partition:
SQL> COL partition_name FORMAT a20 COL high_value FORMAT a50 WORD_WRAPPED SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = ‘TBL_INTERVAL_PART’ ORDER BY partition_position; PARTITION_NAME HIGH_VALUE -------------------- -------------------------------------------------- PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
Now let’s insert a third row with the value greater that the highest boundary value of the partition column. The third partition will be created automatically:
SQL> INSERT INTO tbl_interval_part VALUES (3,’third’,’row’,TO_DATE (‘06062016’, ‘ddmmyyyy’)); 1 row created. SQL> SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = ‘TBL_INTERVAL_PART’ ORDER BY partition_position; PARTITION_NAME HIGH_VALUE -------------------- -------------------------------------------------- PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SQL>
Now, try to insert a row with a few months greater than the highest value of the partition, and see what happens:
SQL> INSERT INTO tbl_interval_part VALUES (4, ‘fourth’,’row’,TO_DATE (‘07092016’,‘ddmmyyyy’)); 1 row created. SQL> SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = ‘TBL_INTERVAL_PART’ ORDER BY partition_position; PARTITION_NAME HIGH_VALUE -------------------- -------------------------------------------------- PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P59 TO_DATE(‘ 2016-10-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
A new partition might also be created in the middle if the date value of the new row is one month less than the boundary partition of the table:
SQL> INSERT INTO tbl_interval_part VALUES (5,’fifth’,’row’,TO_DATE (‘08072016’, ‘ddmmyyyy’)); 1 row created. SQL> SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = ‘TBL_INTERVAL_PART’ ORDER BY partition_position; PARTITION_NAME HIGH_VALUE -------------------- -------------------------------------------------- PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P60 TO_DATE(‘ 2016-08-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SYS_P59 TO_DATE(‘ 2016-10-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA SQL>
Note: If you are asked to create a monthly or yearly partition, use NUMTOYMINTERVAL which converts a given value to an interval year to month literal. When creating a daily or weekly partitions, use NUMTODSINTERVAL which converts a given value to an interval day to second
You can check “Database SQL Language Reference” to get more information about NUMTOYMINTERVAL and NUMTODSINTERVAL functions.
Interval partitioning cannot be specified at subpartition level. You can specify only one partition key with the type either NUMBER or DATE.
Run the following command to disable an interval partitioning on the specific table:
SQL> ALTER TABLE <table name> SET INTERVAL ();
Creating Reference partitioned table
Reference:
Reference Partitioning Examples in Oracle 11G and above (Doc ID 761251.1)
11g Partitioning Enhancements (Doc ID 452447.1)
With parent-child relationship, a child table now can inherit partitioning key from the parent table without duplicating the key columns.
To get the syntax of reference partitioned table, go to the documentation that is provided in the beginning of this section and move to the “Creating Reference-Partitioned Tables” part.
To see how it works, we need to create one parent and one child table. Create a parent table as follows:
SQL> CREATE TABLE TBL_REF_PART ( id NUMBER PRIMARY KEY, name VARCHAR2 (20), surname VARCHAR2 (20) ) PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20)); Table created. SQL>
Now create a child table with foreign key to the parent table and use PARTITION BY REFERENCE syntax to inherit a partitioning from the parent table as follows:
SQL> CREATE TABLE tbl_ref_part_child ( id NUMBER PRIMARY KEY, name VARCHAR2 (20), CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES tbl_ref_part (id) ) PARTITION BY REFERENCE (fk_id); Table created. SQL>
If you query DBA_TAB_PARTITIONS view, you will see the list of partitions that belongs to the child table are created automatically:
SQL> SET LINESIZE 150 SQL> SELECT table_name, partition_name FROM dba_tab_partitions WHERE table_name LIKE ‘TBL_REF%’; TABLE_NAME PARTITION_NAME ------------------------------ -------------------- TBL_REF_PART P1 TBL_REF_PART P2 TBL_REF_PART_CHILD P1 TBL_REF_PART_CHILD P2
Now query DBA_PART_TABLES view to see the foreign key constraint name on which the
partition was created:
SQL> SELECT table_name, partitioning_type, ref_ptn_constraint_name FROM dba_part_tables WHERE table_name LIKE ‘TBL_REF%’; TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME ------------------------------ --------- ------------------------------ TBL_REF_PART RANGE TBL_REF_PART_CHILD REFERENCE FK_ID
Creating System partitioned table
Reference:
Documentation – Unstructured Data and Content Management->Database Data Cartridge Developer’s Guide->Using System Partitioning
11g New Features: System Partitioning (Doc ID 785462.1)
11g Partitioning Enhancements (Doc ID 452447.1)
By using system partitioning option, you don’t define a specific partitioning key and create many physical partitions. When you insert a row, however, you need to specify the partition which will receive the data. Use PARTITION BY SYSTEM syntax of the CREATE TABLE command to create a
system partitioned table as follows:
SQL> CREATE TABLE tbl_sys_part ( id NUMBER, name VARCHAR2 (10), surname VARCHAR2 (10) ) PARTITION BY SYSTEM (PARTITION part1 TABLESPACE SYSTEM, PARTITION part2 TABLESPACE users); Table created. SQL>
Query USER_PART_TABLES view to see the type of the partition:
SQL> SELECT partitioning_type FROM user_part_tables WHERE table_name =’TBL_SYS_PART’ PARTITION --------- SYSTEM SQL>
The following error will appear if you try to insert a row to the table without specifying the partition name:
SQL> INSERT INTO tbl_sys_part VALUES (1, ‘first’, ‘row’); INSERT INTO tbl_sys_part VALUES (1, ‘first’, ‘row’); * ERROR at line 1: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method SQL>
The partition name must be implicitly defined when inserting a row in a table:
SQL> INSERT INTO tbl_sys_part PARTITION (part1) VALUES (1, ‘first’, ‘row’); 1 row created. SQL> INSERT INTO tbl_sys_part PARTITION (part2) VALUES (2, ‘second’, ‘row’); 1 row created.
Partition name might be omitted when deleting a row:
SQL> DELETE tbl_sys_part WHERE id = 1; 1 row deleted.
To delete all rows of the specific partition, the partition name could be specified as follows:
SQL> DELETE tbl_sys_part PARTITION (part2); 1 row deleted. SQL>
Gather statistics on a specific table without invalidating cursors