How to create an oracle Partition Table

Oracle provides Partitioning technology to support VLDB (Very Large DataBase ). The partition table judges the partition columns to store different records in different partitions. Partitions are completely transparent to applications.

Oracle partition tables can contain multiple partitions. Each partition is an independent SEGMENT and can be stored in different tablespaces. You can use a query table to access data in each partition, or you can specify a partition for query.

Partitions provide the following advantages:

Data is distributed to various partitions, which reduces the possibility of data corruption;

You can back up and restore individual partitions;

Partitions can be mapped to different physical disks to distribute IO;

Improves manageability, availability, and performance.

Oracle provides the following Partition types:

Range partition (range );

Hash partition (hash );

List partition (list );

Range-hash composite partition (range-hash );

Range-list composite partition (range-list ).

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.

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

 

Indexes can also be partitioned. There are two types of partition indexes: global and local. For a local index, each table partition corresponds to an index partition. When the table partition changes, the index maintenance is automatically performed by Oracle. For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the global index is usually INVALDED and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to re-create a GLOBAL index while maintaining partitions.

Global indexes can contain values of multiple partitions. Local indexes are easier to manage than global indexes, while global indexes are faster.
Note: you cannot create global indexes for hash partitions or subpartitions.

Oracle has powerful partitioning functions. However, it is not very convenient to use it:

First, there is no way to directly convert an existing table into a partitioned table. However, Oracle provides the online table redefinition function, which can be used to convert normal tables to partitioned tables. Can refer to this example: http://blog.itpub.net/post/468/13091

The second point is that if the local partition index is used, the tablespace of the index partition is uncontrollable when the table partition is added. If you want to separate the table and index partitions into different tablespaces and different index partitions are scattered into different tablespaces, you can only add partitions and rebuild the new partition index separately.

How many partitions Does Oracle Support?

We can find this information from Oracle's Concepts manual, for Oracle9iR2:

Tables can be partitioned into up64,000Separate partitions.

Oracle enhances the partition feature for Oracle10gR2:

Tables can be partitioned into up1024K-1Separate partitions.

Oracle provides the following suggestions on when to partition:

■ Tables greater than 2 GB shoshould always be considered for partitioning.
■ Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.