Exchange Partition in Oracle

Some times we faced a need to convert our existing table from non-partition table to a partition table or vice-versa. Or moving a partition of one partition table to another partition. Or make one of the table as a partition of some partition table. Or make a partition of one of the partition table as a separate table.

To deal with this kind of situation, oracle has introduced exchange partition functionality long time a go (I guess from 8i onwards).
We will see a simple example of how to convert a simple non-partition table to a partition table.

1) Create a table, create index on table and gather stats on table and index


ORCL1>create table t as select * from dba_objects;

Table created.

ORCL1>create index t_idx on t(owner, object_name);

Index created.

ORCL1>exec dbms_stats.gather_table_stats('ADVAITD_DBA','T',cascade=>true);

PL/SQL procedure successfully completed.

ORCL1>select count(1) from t;

 COUNT(1)
----------
 14966

2) Create partition table with basic partition


ORCL1>create table pt (
 2  OWNER           VARCHAR2(30),
 3  OBJECT_NAME     VARCHAR2(128),
 4  SUBOBJECT_NAME  VARCHAR2(30),
 5  OBJECT_ID       NUMBER,
 6  DATA_OBJECT_ID  NUMBER,
 7  OBJECT_TYPE     VARCHAR2(19),
 8  CREATED         DATE,
 9  LAST_DDL_TIME   DATE,
 10  TIMESTAMP       VARCHAR2(19),
 11  STATUS          VARCHAR2(7),
 12  TEMPORARY       VARCHAR2(1),
 13  GENERATED       VARCHAR2(1),
 14  SECONDARY       VARCHAR2(1),
 15  NAMESPACE       NUMBER,
EDITION_NAME    VARCHAR2(30)
 16   17  )
 18  partition by range (CREATED)
 19  (PARTITION PT_2011 VALUES less than (MAXVALUE));

Table created.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 0

3) Exchange the partition with table


ORCL1>alter table pt exchange partition PT_2011 with table T without validation;

Table altered.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 14966

ORCL1>select count(1) from t;

 COUNT(1)
----------
 0

4) Split the partition and check

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2006', 'DD-MON-YYYY'))
INTO (PARTITION PT_2006,
 PARTITION PT_2011)

Analyze the table

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

Check the number of rows in each partition


ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             6047

Like that split again and check the records

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2007', 'DD-MON-YYYY'))
INTO (PARTITION PT_2007,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5937

Do for rest of the partitions


ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2008', 'DD-MON-YYYY'))
INTO (PARTITION PT_2008,
 PARTITION PT_2011)

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2010', 'DD-MON-YYYY'))
INTO (PARTITION PT_2010,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2008                        TO_DATE(' 2008-12-31 00:00:00'        250
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------

PT                             PT_2009                        TO_DATE(' 2009-12-31 00:00:00'        312
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2010                        TO_DATE(' 2010-12-31 00:00:00'        163
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5212

6 rows selected.

Regarding the index, we can create the similar index on partition table as well. We will create it a local index

ORCL1>create index PT_IDX on PT(owner, object_name) local;
Index created.
ORCL1>

This step can be done before as well, but doesnt matter.

Now lets verify the results


ORCL1>select count(1) from dba_objects where created < to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 8919

ORCL1>select count(1) from dba_objects where created < to_date('2007-12-31','YYYY-MM-DD') and created > to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 110

ORCL1>select count(1) from dba_objects where created < to_date('2008-12-31','YYYY-MM-DD') and created > to_date('2007-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 250

ORCL1>select count(1) from dba_objects where created < to_date('2009-12-31','YYYY-MM-DD') and created > to_date('2008-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 312

ORCL1>select count(1) from dba_objects where created < to_date('2010-12-31','YYYY-MM-DD') and created > to_date('2009-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 163

ORCL1>select count(1) from dba_objects where created < to_date('2011-12-31','YYYY-MM-DD') and created > to_date('2010-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 5220

RCATLTN1>

So this is how we can convert a simple non-partition table to a partition table.
Don’t forgot to gather status again after creating all partitions and indexes.

Hope this helps !!

Advertisements

5 thoughts on “Exchange Partition in Oracle

  1. It certainly does help. There is very little practical info like this out there on partitioning and how powerful it can be.
    Bookmarked and thanks!

  2. If you are going to change the table name, then wouldn’t it be easier to use CTAS with partition versus having to perform partition exchange?

  3. basically CTAS will copy the data to new segment. if your base table is huge, it might take more time, space etc. Exchange partitioning will just rename your segment and point to the partition table. Basically both methods works, but exchange partitioning wont require additional space and lot of time will be saved.

    1. I don’t think this is true, split partition will create a new partition segment and move the data from the soure partition to the new partition. Having testing yet, but it it is me, I would go w/ mding’s approach and utilizing query and dml parallelism to speed up the process.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s