Partition : Split and Merge

1.       Split Partition 

A.      Range-Partitioned Table:

ALTER TABLE big_table2 -- Table Name

  SPLIT PARTITION big_table_2003

AT (TO_DATE('31-MAY-2003 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))

  INTO (PARTITION big_table_2003_MAY,

        PARTITION big_table_2003)

  UPDATE GLOBAL INDEXES;

Partition name big_table_2003 had high value 2004-01-01

Split into 2 parts - big_table_2003_MAY (with high value till 2003-05-31) & PARTITION big_table_2003

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

B.      List-Partitioned Table

ALTER TABLE sales_by_region

   SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD')

   INTO

    ( PARTITION region_east_1

         TABLESPACE tbs2,

      PARTITION region_east_2

        STORAGE (INITIAL 8M))

   PARALLEL 5;

PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

 The two new partitions are:

region_east_1 with a literal value list of ('CT','MA','MD')

region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')

ALTER TABLE sales_by_region

   SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID')

   INTO

    ( PARTITION region_wildwest,

      PARTITION region_unknown);

C.      Interval-Partitioned Table

ALTER TABLE transactions

    SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))

    AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'));

2.      Merge Partition. 

Merging Partitions

·       You are allowed to merge the contents of two adjacent range partitions into one partition. Nonadjacent range partitions cannot be merged.

·       You cannot use this statement for a hash-partitioned table or for hash sub partitions of a composite *-hash partitioned table.

·       You cannot merge partitions for a reference-partitioned table

-- Range Partition MERGE

CREATE TABLE four_seasons

(

        one DATE,

        two VARCHAR2(60),

        three NUMBER

)

PARTITION  BY RANGE ( one )

(

PARTITION quarter_one

   VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon-yyyy'))

   ,

PARTITION quarter_two

   VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))

   ,

PARTITION quarter_three

   VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))

   ,

PARTITION quarter_four

   VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))

  

);

ALTER TABLE four_seasons

MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two

UPDATE INDEXES;

-- Interval Partition MERGE

CREATE TABLE transactions

( id               NUMBER

, transaction_date DATE

, value            NUMBER

)

PARTITION BY RANGE (transaction_date)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

( PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')));

INSERT INTO transactions VALUES (4,TO_DATE('15-DEC-2006','dd-MON-yyyy'),50);

INSERT INTO transactions VALUES (1,TO_DATE('15-JAN-2007','dd-MON-yyyy'),100); --This creates the interval partitions for these days has a system-generated name

INSERT INTO transactions VALUES (2,TO_DATE('16-JAN-2007','dd-MON-yyyy'),600); -- This creates the interval partitions for these days has a system-generated name

INSERT INTO transactions VALUES (3,TO_DATE('30-JAN-2007','dd-MON-yyyy'),200);-- This creates the interval partitions for these days has a system-generated name

ALTER TABLE transactions

MERGE PARTITIONS FOR(TO_DATE('15-JAN-2007','dd-MON-yyyy')), FOR(TO_DATE('16-JAN-2007','dd-MON-yyyy'));

-- List partition MERGE

ALTER TABLE q1_sales_by_region

   MERGE PARTITIONS q1_northcentral, q1_southcentral

   INTO PARTITION q1_central

      STORAGE(MAXEXTENTS 20);

-- Merging *-Hash Partitions

ALTER TABLE all_seasons

   MERGE PARTITIONS quarter_1, quarter_2 INTO PARTITION quarter_2

   SUBPARTITIONS 8;

-- Merging *-List Partitions

ALTER TABLE stripe_regional_sales

   MERGE PARTITIONS q1_1999, q2_1999 INTO PARTITION q1_q2_1999

      STORAGE(MAXEXTENTS 20);

-- Merging Subpartitions in a *-List Partitioned Table

ALTER TABLE quarterly_regional_sales

   MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest

      INTO SUBPARTITION q1_1999_west

         TABLESPACE ts4;

-- Merging *-Range Partitions

ALTER TABLE orders

MERGE PARTITIONS FOR(TO_DATE('01-MAR-2007','dd-MON-yyyy')),

FOR(TO_DATE('01-APR-2007','dd-MON-yyyy'))

INTO PARTITION p_pre_may_2007;