Types of Table Partitions in Oracle

In this article we will see types of partitions available in Oracle.

Range Partitioning :

Range Partitioning works with range of column values. It sets lower boundaries and upper boundaries to the partition. It is widely used with DATE data types.

Lets create one range partition to understand it better.It is generally used with DATE columns.

Example :

CREATE TABLE TRANSACTIONS(
  TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER)
PARTITION BY RANGE (DATE_OF_TRANSACTION)
 (PARTITION Y_2011 VALUES LESS THAN
   (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION Y_2012 VALUES LESS THAN
   (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
PARTITION Y_2013 VALUES LESS THAN
   (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
PARTITION Y_2014 VALUES LESS THAN
   (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION Y_2015 VALUES LESS THAN
   (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION Y_2016 VALUES LESS THAN
   (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION Y_2017 VALUES LESS THAN
   (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION Y_2018 VALUES LESS THAN
   (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION Y_2019 VALUES LESS THAN
   (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')));

HASH Partitioning :                                                                                                   

This method uses hash function on the partitioning columns to stripe data into partitions. The hashing algorithms distributes data evenly across the partitions. It is the ideal method of distributing data evenly provided that number of partitions are power of two. 

Example :

SQL> 
CREATE TABLE TRAN_HASH
(TRAN_ID NUMBER,
TRAN_DATE DATE,
AMOUNT NUMBER(10,2),
ACCOUNT_NUMBER NUMBER)
PARTITION BY HASH(TRAN_ID)
PARTITIONS 5
STORE IN (PART1,PART2);SQL>   2    3    4    5    6    7    8  

Table created.

SQL> 

List Partitioning  :

This method allows control over how rows maps to the Partitions. Unlike range partition , List partitions are used to partition discrete unrelated data into partitions. You need to provide list of values for partitions.

Example :

SQL> 
SQL> CREATE TABLE DEPT_LIST
(EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_ID CHAR(3))
PARTITION BY LIST(DEPT_ID)
(PARTITION P1 VALUES('SLS','ACC'),
PARTITION P2 VALUES('HRM','MAR'));  2    3    4    5    6    7  

Table created.

SQL> 

Interval Partition :

In Interval Partitions database automatically create a partition of a specified interval when data is inserted beyond the existing range partitions.
Note : IOT does not support interval partitioning

Example :

SQL> CREATE TABLE TRAN_INTERVAL
  2  (TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2))
PARTITION BY RANGE(DATE_OF_TRAN)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN(TO_DATE('01-05-2019','DD-MM-YYYY')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')));  3    4    5    6    7    8    9   10   11   12  

Table created.

SQL> 

Reference Partitioned Table : 

In reference partitioning table , partitioning works with primary key and foreign key relationship. You do not need to specify partitions at child table. Those are automatically created based on parent table.

Let’s create reference partitions table

Step 1 : Create parent table with primary key.

SQL> CREATE TABLE TRANSACTIONS_MAIN(
  2    TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRAN_PK PRIMARY KEY(TRAN_ID)
)
PARTITION BY RANGE (DATE_OF_TRANSACTION)
 (PARTITION Y_2011 VALUES LESS THAN
   (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION Y_2012 VALUES LESS THAN
   (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
PARTITION Y_2013 VALUES LESS THAN
   (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
PARTITION Y_2014 VALUES LESS THAN
   (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION Y_2015 VALUES LESS THAN
  3    4    5    6    7    8    9   10   11   12   13   14   15     (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION Y_2016 VALUES LESS THAN
   (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION Y_2017 VALUES LESS THAN
   (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION Y_2018 VALUES LESS THAN
   (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION Y_2019 VALUES LESS THAN
   (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))); 16   17   18   19   20   21   22   23   24   25   26  

Table created.

SQL> 

Step 2 : Now create table with reference partitioning 

SQL> 
CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER NOT NULL,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)SQL>   2    3    4    5    6    7  ;

Table created.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp