Introduction to Oracle partition Table technology __oracle

Introduction to Oracle Partitioning table technology

Oracle provides partitioning technology to support VLDB (Very Large DataBase). The partitioned table puts different records of the partition columns into different partitions by judging the partitioning columns.

Partitions are completely transparent to the application. 

Oracle's partitioned tables can include multiple partitions, each of which is a separate segment (SEGMENT) that can be stored in a different table space.

Queries can be queried by querying tables to access the data in each partition, or by specifying the partition directly at the time of the query. 

Partitioning provides the benefits of reducing data corruption by spreading data across partitions, backing up and restoring separate partitions, and mapping partitions to different physical disks to disperse IO, improving manageability, availability, and performance, among other things. 

Oracle provides the following partition types: Range partition (range), hash partition (hash), list partition (lists), range-hash composite partition (Range-hash), range-list composite partition (Range-list). 

Oracle's ordinary tables do not have the means to transform the attributes directly into partitioned tables, must be transformed through reconstruction, the following describes the three methods of high efficiency, and explain their respective characteristics. 

Method One: Rebuild the partition table with the original table. 

Step: sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE); 

Table has been created. 

Sql> INSERT into T-SELECT rownum, CREATED from Dba_objects; 

6264 lines have been created. 

Sql> COMMIT;
Submit completed. Sql> CREATE TABLE t_new (ID, Time) PARTITION by RANGE (time) 2 (PARTITION P1 VALUES less THAN (' 2004-7-1 ') ' Yyyy-mm-dd '), 3 PARTITION P2 values less THAN (to_date (' 2005-1-1 ', ' yyyy-mm-dd ')), 4 PARTITION P3 values less TH 

An (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')), 5 PARTITION P4 VALUES less THAN (MAXVALUE)) 6 as SELECT ID, time from T; 

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 simple and easy to use, because using DDL statements will not produce undo, and only a small amount of redo, efficiency is relatively high, and the completion of the table after the data has been distributed to the various partitions. Insufficient: Additional considerations are needed for the consistency of data. Since there is little way to ensure consistency by manually locking the T-table, the direct modification of the Execute CREATE TABLE statement and the rename t_new to T statement may be lost, and if consistency is to be ensured, the data will need to be checked after the execution of the statement, and the cost is relatively large. 

Additionally, access to T performed between two rename statements fails. 

Suitable for the modification of infrequent tables, in the idle operation, table data should not be too large. 

Method Two: Use the Swap partition method. 

Step: sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE); 

Table has been created. 

Sql> INSERT into T-SELECT rownum, CREATED from Dba_objects; 

6264 lines have been created. 

Sql> COMMIT; 

Submit completed. Sql> CREATE TABLE t_new (ID number PRIMARY KEY, Time DATE) PARTITION by RANGE (time) 2 (PARTITION P1 VALUES less TH 

An (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')), 3 PARTITION P2 VALUES less THAN (MAXVALUE)); 

Table has been created. Sql> ALTER TABLE t_new EXCHANGE PARTITION P1 with TABLE T; 

The table has 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 Advantages: Only the definition of partitions and tables in the data dictionary has been modified, and there is no data modification or replication, the most efficient. If the distribution of data in the partition without further requirements, the implementation is relatively simple. 

After performing the rename operation, you can check the presence of data in the T_old, and if so, insert the data directly into T to ensure that the operation on T is not lost. Insufficient: There is still a consistency problem, the query, update, and delete errors or data cannot be accessed before rename t_new to T after the swap partition. 

If you require data to be distributed across multiple partitions, you need to split the partition, increasing the complexity of the operation and reducing the efficiency. The action for a table that contains a large amount of data to go to a partition in a partitioned table. 

You should try to operate at leisure. 

Method Three: Oracle9i above version, use the online redefinition function step: sql> CREATE TABLE T (ID number PRIMARY KEY, time DATE); 

Table has been created. 

Sql> INSERT into T-SELECT rownum, CREATED from Dba_objects; 

6264 lines have been created. 

Sql> COMMIT; 

Submit completed. Sql> EXEC dbms_redefinition. Can_redef_table (USER, ' T ', dbms_redefinition.) 

CONS_USE_PK); 

The PL/SQL process has completed successfully. Sql> CREATE TABLE t_new (ID number PRIMARY KEY, Time DATE) PARTITION by RANGE (time) 2 (PARTITION P1 VALUES less TH An (to_date (' 2004-7-1 ', ' yyyy-mm-dd ')), 3 PARTITION P2 VALUESLess THAN (to_date (' 2005-1-1 ', ' yyyy-mm-dd ')), 4 PARTITION P3 VALUES less THAN (to_date (' 2005-7-1 ', ' yyyy-mm-dd ')), 
5 PARTITION P4 VALUES less THAN (MAXVALUE)); 

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 has completed successfully. Sql> EXEC dbms_redefinition. 

Finish_redef_table (' yangtk ', ' T ', ' t_new '); 

The PL/SQL process has completed successfully. 

  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 Advantages: Ensure data consistency, for most of the time, the table T can be normal DML operations. Only in the switch of the instantaneous lock table, with high availability. This approach is highly flexible and can be met for a variety of different needs. 

Moreover, you can make the appropriate authorization before switching and establish a variety of constraints, you can do after the handover no longer require any additional management operations. 

Insufficient: the implementation is slightly more complex than the above two kinds. 

Applicable to all situations. 

Here is only one of the simplest examples of online redefinition tables, with detailed descriptions and examples to refer to the following two articles. 

Oracle's online redefinition table feature: http://blog.itpub.net/post/468/12855 Oracle's online redefinition table function (ii): http://blog.itpub.net/post/468/12962 Indexes can also be partitioned, and there are two types of partitioned indexes: GlobaL and Local. For local indexes, each table partition corresponds to an index partition, and when the partition of the table changes, the maintenance of the index is performed automatically by Oracle. For global indexes, you can choose whether to partition, and the partitions of the indexes may not correspond to the table partitions. When the partition is maintained, it typically causes the invalded of the global index, which must be rebuild after the operation is completed. 

Oracle9i provides an update global indexes statement that enables you to rebuild the global index while maintaining the partition. A global index can contain more than one partition. Local indexes are easier to manage than global indexes, while global indexes are relatively quick to note: You cannot create global indexes for a hash partition or a child partition. Oracle has a very powerful partitioning function. However, it is not convenient to use it to find two points: the first is that existing tables have no way to directly convert to partitioned tables. However, Oracle provides the ability to redefine tables online in this way to transform normal tables to partitioned tables. You can refer to this example: http://blog.itpub.net/post/468/13091 2nd, if the local partitioning index is used, the table space of the index partition is not controllable when the table partition is added.

If you want to separate the partitions of tables and indexes into different table spaces and different index partitions are dispersed into different tablespaces, you can only rebuild the new partition index separately after adding the partition.

How many partitions does Oracle maximum allow?

We can find this information from Oracle's Concepts manual for Oracle9ir2:tables can partitioned into the up to 64,000 separate partitions. 

For Oracle10gr2,oracle, the partitioning feature is enhanced: Tables can be partitioned into the up to 1024k-1 separate partitions. 
Oracle has the following recommendations for when partitioning should take place: Tables greater than 2GB should always is considered for partitioning. Tables containing historical data, in which new data are added into the newest partition. A typical example is a historical table where ONly the current month ' s data are updatable and the other months are read only. This information is found on the Internet, the test can be used indeed.