Wednesday, May 13, 2009

Oracle Partitioning - Merge Partition

Oracle allows us to merge 2 partitions into one new partition.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

SQL> alter table DATA_LOAD merge partitions data_01_2009,data_02_2009 into partition data_01_02_2009 TABLESPACE DATA_02;
Table altered.

Merging Subpartitions:

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'))
) ,
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'))
) ,
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)
)
);

SQL> alter table DATA_LOAD merge subpartitions data_03_2009_1,data_03_2009_2 into subpartition data_03_2009_1_2;
Table altered.

Details about Oracle Partition Methods/Types

0 Comments: