Oracle Partitioning - Exchange Partition

The need and use of exchange partitions:

I have used exchange partition to archive old data from an existing partition table. As we have a lot of very large partition tables which store data for past years, we exchange older partitions which don't need to be part of the existing table to a new table and then export it and save it on to a tape device as archive data. This way we are able to store the data out of the database and keep it on tapes for any future use. We can import this data whenever we need. We see that exchange partition is widely used in data warehousing environments to keep the tables in a manageable size, achieve adequate performance apart from data archiving. As using exchange partition we can restore this data back into the old table whenever we need it.
We can use exchange partition to convert a non partitioned table into a partitioned table also.
Syntax and example:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
( PARTITION data_2006 VALUES LESS THAN (TO_DATE('01-Jan-2007','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-Jan-2008','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-Jan-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-Jan-2010','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'CA');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'LA');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'FA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'GA');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'BA');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'RA');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'TA');
1 row created.
SQL> commit;
Commit complete.

SQL> set lines 300
SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA
7 rows selected.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ----------
DATA_LOAD DATA_2006 5
DATA_LOAD DATA_2007 0
DATA_LOAD DATA_2008 1
DATA_LOAD DATA_2009 1

Now lets try and move the DATA_2006 to a new table, lets create the table to exchange the partition with:
SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_2006';
no rows selected

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA

This was an example of single level partition, lets try a table with composite partitions:

SQL> drop table DATA_LOAD;
Table dropped.
SQL> drop table DATA_2006;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_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_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) ,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) ,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'DELHI');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'RAJASTHAN');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'KERELA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'ASSAM');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'NAGALAND');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'PUNJAB');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'HARYANA');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,num_rows from user_tab_subpartitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_2006 DATA_2006_OTHER 0
DATA_LOAD DATA_2006 DATA_2006_NO_REGION 0
DATA_LOAD DATA_2006 DATA_2006_EAST_01 2
DATA_LOAD DATA_2006 DATA_2006_SOUTH_01 1
DATA_LOAD DATA_2006 DATA_2006_WEST_01 1
DATA_LOAD DATA_2006 DATA_2006_NORTH_01 1
DATA_LOAD DATA_2007 DATA_2007_OTHER 0
DATA_LOAD DATA_2007 DATA_2007_NO_REGION 0
DATA_LOAD DATA_2007 DATA_2007_EAST_01 0
DATA_LOAD DATA_2007 DATA_2007_SOUTH_01 0
DATA_LOAD DATA_2007 DATA_2007_WEST_01 0
DATA_LOAD DATA_2007 DATA_2007_NORTH_01 0
DATA_LOAD DATA_2008 DATA_2008_OTHER 0
DATA_LOAD DATA_2008 DATA_2008_NO_REGION 0
DATA_LOAD DATA_2008 DATA_2008_EAST_01 0
DATA_LOAD DATA_2008 DATA_2008_SOUTH_01 0
DATA_LOAD DATA_2008 DATA_2008_WEST_01 0
DATA_LOAD DATA_2008 DATA_2008_NORTH_01 1
DATA_LOAD DATA_2009 DATA_2009_OTHER 0
DATA_LOAD DATA_2009 DATA_2009_NO_REGION 0
DATA_LOAD DATA_2009 DATA_2009_EAST_01 0
DATA_LOAD DATA_2009 DATA_2009_SOUTH_01 0
DATA_LOAD DATA_2009 DATA_2009_WEST_01 0
DATA_LOAD DATA_2009 DATA_2009_NORTH_01 1
24 rows selected.

Now lets create the table to exchange the partition with:

SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.
Now to exchange the partition DATA_2006, with the table DATA_2006:

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table

SQL> !oerr ora 14291
14291, 00000, "cannot EXCHANGE a composite partition with a non-partitioned table"
// *Cause: A composite partition can only be exchanged with a partitioned
// table.
// *Action: Ensure that the table being exchanged is partitioned or that
// that the partition being exchanged is non-composite.


This should that we cannot exchange partition with a normal table like we did the last time, now lets create the correct table:

SQL> drop table DATA_2006;
Table dropped.

We need to create a table with the partition scheme as the subpartition scheme of the main/big table, to be able to exchange its partition with a new table:

SQL> CREATE TABLE DATA_2006
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY LIST (state)
8 (PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
9 PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
10 PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
11 PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
12 PARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
13 PARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
14 );
Table created.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

Just to check how the partitions are laid out on the new table. You can see that the subpartition data has moved to the respective partitions of the exchanged table.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_2006';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_2006 NORTH_01 1 0
DATA_2006 WEST_01 1 0
DATA_2006 SOUTH_01 1 0
DATA_2006 EAST_01 2 0
DATA_2006 NO_REGION 0 0
DATA_2006 OTHER 0 0
6 rows selected.

The main/big table will still have the partition defination but with no rows after we analyze the table. Now we can either leave this empty partition or drop the same.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 0 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

We can restore the data back into the main table also. We can exchange the this newly created table with the empty partition in the big/main table back, incase we ever need the data back into the main table. Hence if we archive data by exchange partition we can restore them back into the main table if we ever need it. Another thing is if we might need to restore we should not drop the empty partitions as its easier to restore the data, else we would need to do a split partition to create the partitions again.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
no rows selected

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA
7 rows selected.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

Useful metalink notes:
Exchange Partitions - Common Problems, Doc ID: 198120.1

SCRIPT: To Automate the Composite-Partitioned Table Exchange, Doc ID: 100701.1

Exchange Partition Having Global Index with Non - Partitioned Table, Doc ID: 419505.1

Note: When we do exchange partition the local indexes become invalid, hence we would need to rebuild the same. If we provide the update global indexes, the global indexes don't become invalid and hence no need to rebuild them.