Convert a common table to a partitioned table

Oracle normal tables cannot be directly converted to partitioned tables by modifying attributes. They must be transformed through reconstruction. The following describes three efficient methods and their respective features.

 

Method 1: use the original table to create a partitioned table.

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (TO_DATE ('2017-1-1 ', 'yyyy-MM-DD ')),
4 PARTITION P3 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
5 PARTITION P4 values less than (MAXVALUE ))
6 as select id, time from t;

The table has been created.

SQL> RENAME T TO T_OLD;

The table has been renamed.

SQL> RENAME T_NEW TO T;

The table has been renamed.

SQL> SELECT COUNT (*) FROM T;

COUNT (*)
----------
6264

SQL> SELECT COUNT (*) FROM T PARTITION (P1 );

COUNT (*)
----------
0

SQL> SELECT COUNT (*) FROM T PARTITION (P2 );

COUNT (*)
----------
6246

SQL> SELECT COUNT (*) FROM T PARTITION (P3 );

COUNT (*)
----------
18

Advantage: The method is simple and easy to use. Because DDL statements are used, UNDO is not generated, and only a small amount of REDO is generated, the efficiency is relatively high, after the table is created, the data is distributed to each partition.

Disadvantages: Additional considerations are required for data consistency. Since there is almost no way TO manually lock the t table to ensure consistency, direct modification TO the execution of the create table statement and the RENAME T_NEW to t statement may be lost. TO ensure consistency, the data needs to be checked after the statement is executed, and the cost is relatively high. In addition, access to T between two RENAME statements fails.

It is applicable to tables that are not frequently modified and operate in idle time. The table data volume should not be too large.

Method 2: Use the swap partition method.

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (MAXVALUE ));

The table has been created.

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

The table has been changed.

SQL> RENAME T TO T_OLD;

The table has been renamed.

SQL> RENAME T_NEW TO T;

The table has been renamed.

SQL> SELECT COUNT (*) FROM T;

COUNT (*)
----------
6264

Advantage: the partition and table definitions in the data dictionary are modified without data modification or replication, which is the most efficient. If there is no further requirement on the distribution of data in the partition, the implementation is relatively simple. After executing the RENAME operation, you can check whether data exists in T_OLD. If so, you can directly Insert the data into T to ensure that the T insertion operation will not be lost.

Insufficient: there is still a consistency problem. Before RENAME T_NEW to t after the partition is switched, the query, update, and deletion errors may occur or data cannot be accessed. If data is required to be distributed to multiple partitions, the SPLIT operation is required, which increases the operation complexity and reduces the efficiency.

This operation is applicable to the conversion from a table with large data volume to a partition in a partitioned table. Perform operations in idle hours as much as possible.

Method 3: Oracle9i or a later version, using the online redefinition Function

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (USER, 't', DBMS_REDEFINITION.CONS_USE_PK );

The PL/SQL process is successfully completed.

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (TO_DATE ('2017-1-1 ', 'yyyy-MM-DD ')),
4 PARTITION P3 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
5 PARTITION P4 values less than (MAXVALUE ));

The table has been created.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, 't', 't_ new ',-
> 'Id ID, TIME time', DBMS_REDEFINITION.CONS_USE_PK );

The PL/SQL process is successfully completed.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('angtk ', 't', 't_ new ');

The PL/SQL process is successfully completed.

SQL> SELECT COUNT (*) FROM T;

COUNT (*)
----------
6264

SQL> SELECT COUNT (*) FROM T PARTITION (P2 );

COUNT (*)
----------
6246

SQL> SELECT COUNT (*) FROM T PARTITION (P3 );

COUNT (*)
----------
18

Advantage: to ensure data consistency, table T can perform DML operations normally most of the time. It only locks the table in the instant of switching and has high availability. This method is flexible and can meet various needs. Additionally, you can grant permissions and create various constraints before the switchover, so that no additional management operations are required after the switchover is complete.

Disadvantages: the implementation is slightly more complex than the above two methods.

Applicable to various situations.

Http://yangtingkun.itpub.net/post/468/13091

Here, we only provide a simple example of an online redefinition table. For detailed descriptions and examples, refer to the following two articles.

Oracle's online redefinition table feature: http://blog.itpub.net/post/468/12855

Oracle online redefinition table feature (2): http://blog.itpub.net/post/468/12962