Interval-* Composite Partitioned Tables

In my previous composite partitioning in oracle article I saw example of various types of composite partitioning methods. In addition to that post today we will see composite partitioning with INTERVAL partitioning type.

To read about previous composite partitioning :

In Interval-* composite partitioning method we will see Interval-Range , Interval-List , Interval-Hash.

1.Create a Interval-range composite partitioned table

SQL> CREATE TABLE TRAN_INTERVAL_range
(TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2))
PARTITION BY RANGE(DATE_OF_TRAN)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(AMOUNT)
SUBPARTITION TEMPLATE
(
SUBPARTITION S1 VALUES LESS THAN(100000),
SUBPARTITION S2 VALUES LESS THAN(500000),
SUBPARTITION S3 VALUES LESS THAN(10000000))
(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')));  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  

Table created.

SQL> 

2.Create a Interval-list composite partitioned table

SQL> CREATE TABLE TRAN_INTERVAL_LIST
  2  (TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2),
TRAN_TYPE CHAR(4)
)
PARTITION BY RANGE(DATE_OF_TRAN)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST(TRAN_TYPE)
SUBPARTITION TEMPLATE
(
SUBPARTITION S1 VALUES ('CASH'),
SUBPARTITION S2 VALUES ('TRF'),
SUBPARTITION S3 VALUES ('CLG'))
(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   13   14   15   16   17   18   19   20  

Table created.

SQL> 

3.Create a Interval-hash composite partitioned table

SQL> CREATE TABLE TRAN_INTERVAL_HASH
  2  (TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2),
TRAN_TYPE CHAR(4)
)
PARTITION BY RANGE(DATE_OF_TRAN)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH(AMOUNT) SUBPARTITIONS 3
(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   13   14   15  

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