Oracle Partitioning Types - III (Composite Partitions)

Composite Partitions:
1.) Composite Range-Range Partitioning
(Available in 11g. Oracle 10g would give error: ORA-14151)

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.


2.) Composite Range-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY HASH (source_id)
SUBPARTITIONS 4 STORE IN (DATA_01, DATA_02, DATA_03, DATA_04)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.


3.) Composite Range-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE (
SUBPARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
SUBPARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
SUBPARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
SUBPARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
SUBPARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
SUBPARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY'))
);
Table created.


4.) Composite List-Range Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY RANGE (load_date)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
( SUBPARTITION n_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION n_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION n_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION n_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
( SUBPARTITION w_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION w_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION w_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION w_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
( SUBPARTITION s_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION s_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION s_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION s_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
( SUBPARTITION e_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION e_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION e_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION e_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION NO_REGION VALUES (NULL)
( SUBPARTITION no_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION no_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION no_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION no_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION OTHER VALUES (DEFAULT)
( SUBPARTITION o_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION o_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION o_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION o_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
)
);
Table created.


5.) Composite List-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY HASH (load_date) SUBPARTITIONS 4
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.


6.) Composite List-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
status VARCHAR2(1),
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY LIST (status)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
(SUBPARTITION a_NORTH_01 VALUES ('A'),
SUBPARTITION b_NORTH_01 VALUES ('B'),
SUBPARTITION c_NORTH_01 VALUES ('C')
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
(SUBPARTITION a_WEST_01 VALUES ('A'),
SUBPARTITION b_WEST_01 VALUES ('B'),
SUBPARTITION c_WEST_01 VALUES ('C')
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
(SUBPARTITION a_SOUTH_01 VALUES ('A'),
SUBPARTITION b_SOUTH_01 VALUES ('B'),
SUBPARTITION c_SOUTH_01 VALUES ('C')
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
(SUBPARTITION a_EAST_01 VALUES ('A'),
SUBPARTITION b_EAST_01 VALUES ('B'),
SUBPARTITION c_EAST_01 VALUES ('C')
),
PARTITION NO_REGION VALUES (NULL)
(SUBPARTITION a_NO_REGION_01 VALUES ('A'),
SUBPARTITION b_NO_REGION_01 VALUES ('B'),
SUBPARTITION c_NO_REGION_01 VALUES ('C')
),
PARTITION OTHER VALUES (DEFAULT)
(SUBPARTITION a_OTHER_01 VALUES ('A'),
SUBPARTITION b_OTHER_01 VALUES ('B'),
SUBPARTITION c_OTHER_01 VALUES ('C')
)
);
Table created.

Part I
Part II
Part IV
Part V
Part VI
Part VII