Oracle Exchange Partitioning Explored

You can convert a partition or subpartition into a nonpartitioned table, and a nonpartitioned table into a partition or subpartition of a partitioned table by exchanging their data segments. You can also convert a hash partitioned table into a partition of a composite *-hash partitioned table, or convert the partition of a composite *-hash partitioned table into a hash partitioned table. Similarly, you can convert a range- or list-partitioned table into a partition of a composite *-range or -list partitioned table, or convert a partition of the composite *-range or -list partitioned table into a range- or list-partitioned table.

Exchanging table partitions is useful to get data quickly in or out of a partitioned table. For example, in data warehousing environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table.

OLTP and data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterward.

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged with the INCLUDING INDEXES clause, and if rows are to be validated for proper mapping with the WITH VALIDATION clause.

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.

You must use the UPDATE GLOBAL INDEXES or UPDATE INDEXES on the exchange partition of the parent table in order for the primary key index to remain usable. 

To avoid the overhead of this validation activity, issue the following statement for each constraint before performing the exchange partition operation:

ALTER TABLE table_name
     DISABLE CONSTRAINT constraint_name KEEP INDEX


Enable the constraints after the exchange.

Partitioning an Existing Table using EXCHANGE PARTITION

This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax. We have a a non-partitioned table BIG_TABLE and we will be converting it into Range Partitioned table BIG_TABLE2. 

Create a Sample Schema
First we create a sample schema as our starting point.

-- Create and populate a small lookup table.
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Create a Partitioned Destination Table
Next we create a new table with the appropriate partition structure to act as the destination table. The destination must have the same constraints and indexes defined.

-- Create partitioned table.
CREATE TABLE big_table2 (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

With this destination table in place we can start the conversion.

EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.

ALTER TABLE big_table2
  EXCHANGE PARTITION big_table_2007
  WITH TABLE big_table
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

The exchange operation should not be affected by the size of the segments involved.

Once this is complete we can drop the old table and rename the new table and all it's constraints.

DROP TABLE big_table;

RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required.

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2005,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2006,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

The following queries show that the partitioning was successful.
SELECT partitioned
FROM   user_tables
WHERE  table_name = 'BIG_TABLE';

PAR
---
YES

1 row selected.

SELECT partition_name, num_rows
FROM   user_tab_partitions
WHERE  table_name = 'BIG_TABLE';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005                     335326
BIG_TABLE_2006                     332730
BIG_TABLE_2007                     334340


3 rows selected.

Exchanging a Range-Partitioned Table with a *-Range Partition

The semantics of the ALTER TABLE EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash Partitioned Table with a *-Hash Partition". The example below shows the orders table, which is interval partitioned by order_date, and subpartitioned by range on order_total. The example shows how to exchange a single monthly interval with a range-partitioned table.

CREATE TABLE orders_mar_2007
( id          NUMBER
, cust_id     NUMBER
, order_date  DATE
, order_total NUMBER
)
PARTITION BY RANGE (order_total)
( PARTITION p_small VALUES LESS THAN (1000)
, PARTITION p_medium VALUES LESS THAN (10000)
, PARTITION p_large VALUES LESS THAN (100000)
, PARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)
);

Populate the table with orders for March 2007. Then create an interval-range partitioned table:

CREATE TABLE orders
( id          NUMBER
, cust_id     NUMBER
, order_date  DATE
, order_total NUMBER
)
PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  SUBPARTITION BY RANGE (order_total)
  SUBPARTITION TEMPLATE
  ( SUBPARTITION p_small VALUES LESS THAN (1000)
  , SUBPARTITION p_medium VALUES LESS THAN (10000)
  , SUBPARTITION p_large VALUES LESS THAN (100000)
  , SUBPARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)
  )
(PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-
MON-yyyy')));

It is important that the partitioning key in the orders_mar_2007 table matches the subpartitioning key in the orders table.

Next, exchange the partition. Because an interval partition is to be exchanged, the partition is first locked to ensure that the partition is created.

LOCK TABLE orders PARTITION FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy')) 
IN SHARE MODE;

ALTER TABLE orders
EXCHANGE PARTITION
FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
WITH TABLE orders_mar_2007

WITH VALIDATION;