Composite Partitioning in Oracle

In this article we will see examples of various types of composite partitions

1.RANGE-HASH composite partitioning :

SQL> CREATE TABLE TRANSACTIONS_MAIN_COMP_HASH(
  2    TRAN_ID NUMBER,
  DATE_OF_TRANSACTION DATE,
  AMOUNT VARCHAR2(100),
  ACCOUNT_NUMBER NUMBER,
CONSTRAINT TRAN_MAIN_COMP_HASH_PK PRIMARY KEY(TRAN_ID)
)
PARTITION BY RANGE (DATE_OF_TRANSACTION) SUBPARTITION BY HASH(ACCOUNT_NUMBER) SUBPARTITIONS 4
(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')),
  3    4    5    6    7    8    9   10   11   PARTITION Y_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY'))); 12   13   14   15   16   17  

Table created.

SQL> 

2.LIST-HASH composite partitioned table

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

Table created.

SQL> 

3.LIST-RANGE Partitioned table :

SQL> CREATE TABLE DEPT_LIST_RANGE
  2  (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY LIST(DEPT_ID) SUBPARTITION BY RANGE(AGE) 
(PARTITION P1 VALUES('SLS','ACC')
(SUBPARTITION A1 VALUES LESS THAN (25),
SUBPARTITION A2 VALUES LESS THAN (35),
SUBPARTITION A3 VALUES LESS THAN (50),
SUBPARTITION A_MAX VALUES LESS THAN (MAXVALUE)),
PARTITION P2 VALUES('HRM','MAR')
(SUBPARTITION B1 VALUES LESS THAN (25),
SUBPARTITION B2 VALUES LESS THAN (35),
SUBPARTITION B3 VALUES LESS THAN (50),
SUBPARTITION B_MAX VALUES LESS THAN (MAXVALUE)
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  ));

Table created.

SQL> 

4.LIST-LIST partitioned table

SQL> CREATE TABLE DEPT_LIST_LIST
  2  (DEPT_CD CHAR(3),
DEPT_NM VARCHAR2(100),
DEPT_REGION CHAR(2),
OTH_DTL VARCHAR2(100))
PARTITION BY LIST(DEPT_REGION)
SUBPARTITION BY LIST(DEPT_CD)
(PARTITION P_L1 VALUES('GJ','RJ')
(SUBPARTITION P_L1_L1 VALUES('AHM'),
SUBPARTITION P_L1_L2 VALUES('GAN'),
SUBPARTITION P_L1_L3 VALUES('JAI')
),
PARTITION P_L2 VALUES('MH')
(SUBPARTITION P_L2_L1 VALUES('MUM'),
 SUBPARTITION P_L2_L2 VALUES('PUN')
));  3    4    5    6    7    8    9   10   11   12   13   14   15   16  

Table created.

SQL> 

5.RANGE-LIST Partitioned table :

SQL> CREATE TABLE DEPT_RANGE_LIST
  2  (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY RANGE(AGE) SUBPARTITION BY LIST(DEPT_ID) 
(PARTITION P_R1 VALUES LESS THAN(25)
(SUBPARTITION P_R1_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R1_L2 VALUES('HRM','MAR')
),
PARTITION P_R2 VALUES LESS THAN(35)
(SUBPARTITION P_R2_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R2_L2 VALUES('HRM','MAR')
),
PARTITION P_R3 VALUES LESS THAN(MAXVALUE)
(SUBPARTITION P_R3_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R3_L2 VALUES('HRM','MAR')
));  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  

Table created.

SQL> 

6.RANGE-RANGE Partitioned table :

SQL> CREATE TABLE SUBSCRIPTION_DTL
  2  (ID NUMBER,
NAME VARCHAR2(50),
REGISTER_DT DATE,
JOINING_DT DATE)
PARTITION BY RANGE (REGISTER_DT)
SUBPARTITION BY RANGE(JOINING_DT)
(PARTITION P_RR1 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy'))
 (SUBPARTITION P_RR1_R1 VALUES LESS THAN (TO_DATE('31-JAN-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR1_R2 VALUES LESS THAN (TO_DATE('28-FEB-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR1_R3 VALUES LESS THAN (TO_DATE('31-MAR-2019','dd-MON-yyyy'))
 ),
 PARTITION P_RR2 VALUES LESS THAN (TO_DATE('01-APR-2019','dd-MON-yyyy'))
 (SUBPARTITION P_RR2_R1 VALUES LESS THAN (TO_DATE('30-APR-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR2_R2 VALUES LESS THAN (TO_DATE('31-MAY-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR2_R3 VALUES LESS THAN (TO_DATE('30-JUN-2019','dd-MON-yyyy'))
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   ));

Table created.

SQL> 

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