How to convert a common table from an Oracle database to a partitioned table (1)

Guidance:In a high-availability system, changing the definition of a table is a tough issue, especially for a 7 × 24 system.Oracle DatabaseThe basic syntax provided can basically meet the general modification requirements. However, if you change a normal heap table to a partition table, you cannot modify the index organization table to a heap 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

Advantages:

The method is 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 already 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.