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 