[Oracle] Oracle Database partition Table Operation method

Summary: In a large number of business data processing projects, you can consider the use of partitioned tables to improve the performance of the application system and facilitate data management, this article describes the use of partitioned tables in detail.

In large enterprise applications or enterprise-class database applications, the amount of data to be processed can typically reach dozens of to hundreds of GB, or even to terabytes. Although storage media and data processing technologies are fast growing, they still do not meet the needs of users, and Oracle provides partitioning of tables and indexes to improve the performance of large application systems in order to make the user's large amount of data faster in both read and write operations and queries.

Advantages of using partitions:

• Enhanced Availability: If one of the table's partitions fails, the table's data in other partitions is still available;

• Easy maintenance: If a section of the table fails, you need to repair the data, only the partition can be repaired;

• Balanced I/O: can map different partitions to disk to balance I/O, improve the performance of the entire system;

• Improve query performance: Queries on partitioned objects can only search for areas of concern to them, improving retrieval speed.

The Oracle database provides three ways to partition a table or index:

• Range Partitioning

· hash partition (hash partition)

• Composite partitions

These three partitioning methods are described in the following example as examples to illustrate the use of partitioned tables. For the convenience of testing, we build three table spaces first.

The following are the referenced contents: Create Tablespace DINYA_SPACE01 DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA01.DNF ' size 50M Create Tablespace DINYA_SPACE01 DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA02.DNF ' size 50M Create Tablespace DINYA_SPACE01 DataFile '/TEST/DEMO/ORACLE/DEMODATA/DINYA03.DNF ' size 50M

　　 1.1. Partition Table Creation

1.1.1. Range Partitioning

A range partition is a range of values in a datasheet that determines which partition the data is stored on, depending on the scope of the value. If according to the ordinal partition, according to the business record creation date carries on the partition and so on.

Requirements Description: There is a material Transaction table, table name: Material_transactions. The table may have a tens number of data records in the future. The partition table is required to be used when the table is being built. At this point, we can use the ordinal partition three areas, each area is expected to store 30 million of the data, you can also use the date partition, such as every five years of data stored on a partition.

To build a table based on the serial number of the transaction:

The following are the referenced contents: Sql> CREATE TABLE Dinya_test 2 ( 3 transaction_id number primary key, 4 item_id Number (8) is not NULL, 5 item_description VARCHAR2 (300), 6 Transaction_date date NOT NULL 7) 8 partition by range (transaction_id) 9 ( Partition part_01 values less than (30000000) tablespace dinya_space01, Partition part_02 values less than (60000000) tablespace dinya_space02, Partition part_03 values less than (MaxValue) tablespace dinya_space03 13); Table created.

The table is successful, according to the serial number of the transaction, the record of the transaction ID below 30 million will be stored in the first tablespace dinya_space01, the partition name is: PAR_01, and the record between 30 million and 60 million is stored in the second table space:

In Dinya_space02, the partition name is: Par_02, and the record of the transaction ID above 60 million is stored in the third tablespace dinya_space03, and the partition name is par_03.

To create a table based on the transaction date:

The following are the referenced contents: Sql> CREATE TABLE Dinya_test 2 ( 3 transaction_id number primary key, 4 item_id Number (8) is not NULL, 5 item_description VARCHAR2 (300), 6 Transaction_date date NOT NULL 7) 8 partition by range (transaction_date) 9 ( Partition part_01 values less than (to_date (' 2006-01-01 ', ' yyyy-mm-dd ')) Tablespace DINYA_SPACE01, One partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')) Tablespace DINYA_SPACE02, Partition part_03 values less than (MaxValue) tablespace dinya_space03 13); Table created.

This allows us to separate the partition tables that are partitioned by transaction number and transaction date. Each time the data is inserted, the record is automatically stored in the defined partition (Tablespace) based on the value of the specified field.

Of course, we can also use the range distribution of two fields to partition according to the requirement, such as partition by range (transaction_id, transaction_date), the values in the partition condition are also changed accordingly, ask the reader to test themselves.

1.1.2. hash partition (hash partition)

A hash partition is a type of partition that distributes data evenly by specifying the partition number, because these partitions are identical in size by hashing partitions on the I/O device. If the data for the Item Transaction table is hashed in the specified three tablespace according to the transaction ID:

The following are the referenced contents: Sql> CREATE TABLE Dinya_test 2 ( 3 transaction_id number primary key, 4 item_id Number (8) is not NULL, 5 item_description VARCHAR2 (300), 6 Transaction_date Date 7) 8 partition by hash (transaction_id) 9 ( Partition part_01 tablespace dinya_space01, One partition part_02 tablespace dinya_space02, Partition part_03 tablespace dinya_space03 13); Table created.

The table succeeds, the data is inserted at this time, the system will be transaction_id to insert records into three partitions, this is the three different table spaces.

1.1.3. Composite partitions

Sometimes when we need to partition the range, the data in each partition is scattered in several tablespaces, so we use a composite partition. A composite partition is a partitioning method that uses a range partition and then uses a hash partition within each partition, such as dividing records for item transactions by time, and then dividing data in each partition into three sub partitions, where the data is hashed in three specified table spaces:

The following are the referenced contents: Sql> CREATE TABLE Dinya_test 2 ( 3 transaction_id number primary key, 4 item_id Number (8) is not NULL, 5 item_description VARCHAR2 (300), 6 Transaction_date Date 7) 8 partition by Range (transaction_date) subpartition by hash (transaction_id) 9 Subpartitions 3 Store in (DINYA_SPACE01,DINYA_SPACE02,DINYA_SPACE03) 10 ( One partition part_01 values less than (to_date (' 2006-01-01 ', ' yyyy-mm-dd ')), Partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')) Partition part_03 values less than (MaxValue) 14); Table created.

In this example, the range partition is first based on the transaction date, and then the records are hashed in three tablespaces based on the transaction ID.

1.2. partition Table Operation

For a table of three partitioned tables, the following uses the actual data and tests the data records of the partitioned tables for the range partition by date.

1.2.1. Insert Record:

The following are the referenced contents: sql> INSERT into dinya_test values (1,12, ' books ', sysdate); 1 row created. sql> INSERT into dinya_test values (2,12, ' books ', sysdate+30); 1 row created. sql> INSERT into dinya_test values (3,12, ' books ', To_date (' 2006-05-30 ', ' yyyy-mm-dd ')); 1 row created. sql> INSERT into dinya_test values (4,12, ' books ', To_date (' 2007-06-23 ', ' yyyy-mm-dd ')); 1 row created. sql> INSERT into dinya_test values (5,12, ' books ', To_date (' 2011-02-26 ', ' yyyy-mm-dd ')); 1 row created. sql> INSERT into dinya_test values (6,12, ' books ', To_date (' 2011-04-30 ', ' yyyy-mm-dd ')); 1 row created. Sql> commit; Commit complete. Sql>

According to the table above, data from 2006 years ago will be stored on the first partition part_01, while the transaction data from 2006 to 2010 will be stored on the second partition part_02, and after 2010, the records are stored on the third partition part_03.


1.2.2. Query partition Table records:

The following are the referenced contents: Sql> SELECT * from Dinya_test partition (PART_01); transaction_id item_id item_description transaction_date -------------------------------------------------------------------------------- 1 Books 2005-1-14 14:19: 2 books 2005-2-13 14:19: Sql> Sql> SELECT * from Dinya_test partition (PART_02); transaction_id item_id item_description transaction_date -------------------------------------------------------------------------------- 3 Books, 2006-5-30 4 Books, 2007-6-23 Sql> Sql> SELECT * from Dinya_test partition (PART_03); transaction_id item_id item_description transaction_date -------------------------------------------------------------------------------- 5 Books, 2011-2-26 6 Books, 2011-4-30 Sql>

From the results of the query, you can see that the inserted data has been stored in different partitions according to the trading time range. This is the query that specifies the partition, and of course you can execute the SELECT * from Dinya_test query all records directly without specifying the partition.

When the amount of data that is also retrieved is large, the specified partition can greatly improve the retrieval speed.

1.2.3. Update the records for the partition table:

The following are the referenced contents: sql> update dinya_test partition (PART_01) t set t.item_description= ' DESK ' where t.transaction_id=1; 1 row updated. Sql> commit; Commit complete. Sql>

Here, the Item_description field in the record of the transaction id=1 in the first partition is updated to "DESK" to see that a record has been successfully updated. However, when the partition is specified when the update is made, and the data is not updated according to the query's record, see the following example:

The following are the referenced contents: sql> update dinya_test partition (PART_01) t set t.item_description= ' DESK ' where t.transaction_id=6; 0 rows updated. Sql> commit; Commit complete. Sql>

Specifies that records are updated in the first partition, but in conditions where the transaction ID is 6, and the query whole table, transaction ID 6 is in the third partition so that the statement will not update the record.

1.2.4. Delete Partition Table records:

The following are the referenced contents: sql> Delete from dinya_test partition (part_02) t where t.transaction_id=4; 1 row deleted. Sql> commit; Commit complete. Sql>

The example above deletes a record with a transaction ID of 4 in the second partition part_02, the same as the update data, and no data is deleted if the partition is specified and the data in the condition is not in the partition.

1.3. Use of partitioned table indexes:

Partitioned tables and general tables can be indexed, and partitioned tables can create local indexes and global indexes. A global index is used when there are many transactions in the partition and the uniqueness of the data records in all partitions is ensured.

1.3.1. Establishment of local index partitions:

The following are the referenced contents: Sql> CREATE index dinya_idx_t on dinya_test (item_id) 2 Local 3 ( 4 partition idx_1 tablespace dinya_space01, 5 partition idx_2 tablespace dinya_space02, 6 Partition Idx_3 tablespace dinya_space03 7); Index created. Sql>

Looking at the execution plan for the query, you can see from the following execution plan that the index is already used by the system:

The following are the referenced contents: Sql> SELECT * from Dinya_test partition (part_01) t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=187) 1 0 TABLE ACCESS (by the local INDEX ROWID) of ' dinya_test ' (cost= 2 card=1 bytes=187) 2 1 INDEX (RANGE SCAN) of ' dinya_idx_t ' (non-unique) (cost=1 card=1) Statistics ---------------------------------------------------------- 0 Recursive calls 0 db Block gets 4 consistent gets 0 physical Reads 0 Redo Size 334 Bytes sent via sql*net to client 309 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 1 Sorts (memory) 0 Sorts (disk) 2 rows processed Sql>

1.3.2. Establishment of global index partitions

When global indexing is established, the global clause allows you to specify the range value of the index, which is the range value of the indexed field:

The following are the referenced contents: Sql> CREATE index dinya_idx_t on dinya_test (item_id) 2 Global partition by range (ITEM_ID) 3 ( 4 partition idx_1 values less than (1000) tablespace dinya_space01, 5 partition Idx_2 values less than (10000) tablespace DINYA_SPACE02, 6 partition Idx_3 values less than (MaxValue) tablespace dinya_space03 7); Index created. Sql>

In this case, the table's item_id field is indexed, and of course you can index the entire table without specifying the index partition name, such as:

The following are the referenced contents: Sql> CREATE index dinya_idx_t on dinya_test (item_id); Index created. Sql>

Similarly, for a global index, you can see that the index is already available based on the execution plan:

The following are the referenced contents: Sql> SELECT * from Dinya_test t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT optimizer=choose (cost=2 card=3 bytes=561) 1 0 TABLE ACCESS (by GLOBAL INDEX ROWID) of ' dinya_test ' (cost =2 card=3 bytes=561) 2 1 INDEX (RANGE SCAN) of ' dinya_idx_t ' (non-unique) (cost=1 card=3) Statistics ---------------------------------------------------------- 5 Recursive calls 0 db Block gets Ten consistent gets 0 physical Reads 0 Redo Size 420 Bytes sent via sql*net to client 309 Bytes received via sql*net from client 2 sql*net roundtrips To/from Client 3 Sorts (memory) 0 Sorts (disk) 5 rows processed Sql>

1.4. Maintenance of partition Table:

After understanding the establishment of partitioned tables, the establishment of indexes, the use of tables and indexes, the maintenance and management of partitions are often applied. The day-to-day maintenance and management of the content includes: adding a partition, merging a partition and deleting partitions, and so on. The following are examples of general operations for adding, merging, and deleting partitions, as an example of a range partition:

1.4.1. Add a partition:

The following are the referenced contents: Sql> ALTER TABLE Dinya_test 2 Add partition part_04 values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')) Tablespace Dinya_spa CE03; Table altered. Sql>

When adding a partition, the condition of the increased partition must be greater than the maximum value of the existing partition, or the system will prompt ORA-14074 partition bound must collate higher than the last partition error.

1.4.2. Merge One partition:

The following are the referenced contents: sql> ALTER TABLE dinya_test merge partitions part_01,part_02 into partition part_02; Table altered. Sql>

In this example, the original table's part_01 partition and part_02 partition are merged, the merged partition is part_02, if the merged partition is part_01 when merging, the system will prompt ORA-14275 cannot reuse Lower-bound partition as resulting partition error.

1.4.3. Delete partition:

The following are the referenced contents: sql> ALTER TABLE dinya_test drop partition part_01; Table altered. Sql>

After you delete a partition of a partitioned table, when you query the data for that table, it shows that the data in that partition is all lost, so be careful when you perform the Delete partition action, make sure that you back up the data before you do it, or merge the partitions.

　　 1.5. Summary:

It should be noted that this article in the example of the name of the partition table transaction operations, the partition is specified, because the partition is specified, the system in the execution of the partition only to operate the records, improve the speed of data processing. It is also possible to not specify that the partition directly manipulate the data. Indexes are indexed on partitioned tables and multiple indexes are used as well as non partitioned tables. In addition, because you may have a certain impact on the partitioning index when maintaining partitions, you may need to rebuild the index after maintenance, and refer to the document in the index section of the partition table