Online conversion of non-partitioned table to partitioned table

Converting a non-partitioned table to a partitioned can be a pain; to do this online one could use dbms_redefinition which has improved with latter releases.

 

However, with Oracle database 12.2 this is now an even easier, more flexible method of performing such a conversion.

 

Let’s create a table and couple of index on it.

 

demo@ORA12C> create table t

  2  as

  3  select *

  4  from all_objects;

 

Table created.

 

demo@ORA12C> create index t_idx_01 on t(object_name,created);

 

Index created.

 

demo@ORA12C> create index t_idx_02 on t(object_type,object_id);

 

Index created.

 

demo@ORA12C> alter table t

  2  add constraint t_pk

  3  primary key(object_id);

 

Table altered.

 

 

To convert a non-partitioned table to a partitioned table online, we can now use this new extension to the ALTER TABLE syntax.

 

 

demo@ORA12C> alter table t modify

  2  partition by range(created)

  3  ( partition p2015 values less than

  4     (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  5    partition p2016 values less than

  6     (to_date('01-Jan-2017','dd-mon-yyyy')) ,

  7    partition p2017 values less than

  8     (to_date('01-Jan-2018','dd-mon-yyyy')) ) ONLINE;

 

Table altered.

 

 

We now have a table that is range partitioned based on the CREATED column and this conversion was performed online.

 

 

demo@ORA12C> select table_name,partitioned

  2  from user_tables

  3  where table_name ='T';

 

TABLE_NAME PAR

---------- ---

T          YES

 

demo@ORA12C> select table_name,partition_name

  2  from user_tab_partitions

  3  where table_name ='T';

 

TABLE_NAME PARTITION_

---------- ----------

T          P2015

T          P2016

T          P2017

 

demo@ORA12C> select table_name,partitioning_type,status

  2  from user_part_tables

  3  where table_name ='T';

 

TABLE_NAME PARTITION STATUS

---------- --------- --------

T          RANGE     VALID

 

 

It is not only the table is now partitioned, but all index remains valid. Any index having the partition key column, will implicitly converted to a LOCAL partitioned index.

 

 

demo@ORA12C> select index_name,partitioned,status

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME PAR STATUS

---------- --- --------

T_IDX_01   YES N/A

T_IDX_02   NO  VALID

T_PK       NO  VALID

 

demo@ORA12C> select index_name,partition_name,status

  2  from user_ind_partitions

  3  where index_name like 'T%';

 

INDEX_NAME PARTITION_ STATUS

---------- ---------- --------

T_IDX_01   P2015      USABLE

T_IDX_01   P2016      USABLE

T_IDX_01   P2017      USABLE

 

demo@ORA12C> select index_name,partitioning_type,locality

  2  from user_part_indexes

  3  where table_name ='T';

 

INDEX_NAME PARTITION LOCALI

---------- --------- ------

T_IDX_01   RANGE     LOCAL

 

demo@ORA12C>

 

 

 

As part of the table conversion syntax, we have the option to also update all the associated indexes and partition them in any manner we may want. For example:

 

demo@ORA12C> alter table t modify

  2  partition by range(created)

  3  ( partition p2015 values less than

  4     (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  5    partition p2016 values less than

  6     (to_date('01-Jan-2017','dd-mon-yyyy')) ,

  7    partition p2017 values less than

  8     (to_date('01-Jan-2018','dd-mon-yyyy')) ) ONLINE

  9  update indexes(

 10     t_pk global partition by hash(object_id)

 11        ( partition hp1, partition hp2) );

 

Table altered.

 

demo@ORA12C>

 

 

In this example, not only we are converting the non-partitioned table to be partitioned, but we are also explicitly converting the primary key index into a global hash partitioned index.

 

If we look at the definition of these indexes, we see that they also have all been converted to partitioned indexes online along with the table.

 

 

demo@ORA12C> select index_name,partitioned,status

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME PAR STATUS

---------- --- --------

T_IDX_01   YES N/A

T_IDX_02   NO  VALID

T_PK       YES N/A

 

demo@ORA12C> select index_name,partition_name,status

  2  from user_ind_partitions

  3  where index_name like 'T%';

 

INDEX_NAME PARTITION_ STATUS

---------- ---------- --------

T_IDX_01   P2015      USABLE

T_IDX_01   P2016      USABLE

T_IDX_01   P2017      USABLE

T_PK       HP1        USABLE

T_PK       HP2        USABLE

 

demo@ORA12C> select index_name,partitioning_type,locality

  2  from user_part_indexes

  3  where table_name ='T';

 

INDEX_NAME PARTITION LOCALI

---------- --------- ------

T_IDX_01   RANGE     LOCAL

T_PK       HASH      GLOBAL

 

demo@ORA12C>

 

 

 

For those of you with the partitioning database options, the ability in 12.2 to now so easily convert a non-partitioned table to be partitioned, along with its associated indexes is just brilliant.