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 !!
Persistent Storage Solutions 
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!
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?
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.
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.
Excellent examples, thanks!