Partition Table operations in Oracle databases (applicable to large data volumes or TB-level databases in a single table)

Summary: In a large number of businesses DataYou can consider Using Partitioned Tables to improve the performance and convenience of the application system. DataManagement, this article describes in detail the use of partition tables.

For large enterprise applications or enterprise-levelDatabaseIn the application,DataThe data volume can usually reach dozens to hundreds of GB, and some can even reach TB level. Although the storage media andDataThe processing technology has developed rapidly, but it still cannot meet the needs of users.DataReading and Writing operations and queries are faster,OracleIt provides techniques for partitioning tables and indexes to Improve the Performance of large-scale application systems.

Advantages of using partitions:

· Enhanced availability: If a table partition failsDataStill available;

· Easy maintenance: If a partition of the table fails, it must be repaired.Data, Only the partition can be repaired;

· Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance;

· Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.

　　OracleThe database provides three partitioning methods for tables or indexes:

· Range partitioning

· Hash partition (hash partition)

· Composite Partition

The following describes how to use the three partition methods as an instance. For test convenience, we first create three tablespaces.

Create tablespace dinya_space01 Datafile '/test/demo/Oracle/DemoData/dinya01.dnf' size 50 m Create tablespace dinya_space01 Datafile '/test/demo/Oracle/DemoData/dinya02.dnf' size 50 m Create tablespace dinya_space01 Datafile '/test/demo/Oracle/DemoData/dinya03.dnf' size 50 m

　　1.1. Create a partition table

1.1.1. Range partitioning

Range partition isDataPartitions the range of a value in the table.DataThe partition on which the data is stored. For example, partition by serial number based on the creation date of business records.

Requirement: there is a material transaction table named material_transactions. This table may have tens of millions in the futureDataNumber of records. The partition table must be used when the table is created. At this time, we can use the serial number to partition three zones, each of which is expected to store 30 millionData, You can also use the date partition, such as every five yearsDataStored in a partition.

Create a table based on the sequence number of the transaction record:

SQL> Create Table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number (8) Not null, 5 item_description varchar2 (300 ), 6 transaction_date date not null 7) 8 partition by range (transaction_id) 9 ( 10 partition part_01 values less than (30000000) tablespace dinya_space01, 11 partition part_02 values less than (60000000) tablespace dinya_space02, 12 partition part_03 values less than (maxvalue) tablespace dinya_space03 13 ); Table created.

The table is successfully created. Based on the transaction sequence number, records with a transaction ID less than 30 million will be stored in the first tablespace dinya_space01 with the partition name: par_01, records Between 30 million and 60 million are stored in the second tablespace:

In dinya_space02, the partition name is par_02, while the transaction ID is more than 60 million records are stored in the third tablespace dinya_space03, And the partition name is par_03.

Create a table based on the transaction date partition:

SQL> Create Table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number (8) Not null, 5 item_description varchar2 (300 ), 6 transaction_date date not null 7) 8 partition by range (transaction_date) 9 ( 10 partition part_01 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')) Tablespace dinya_space01, 11 partition part_02 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')) Tablespace dinya_space02, 12 partition part_03 values less than (maxvalue) tablespace dinya_space03 13 ); Table created.

In this way, we have created a partition table partitioned by the transaction sequence number and transaction date respectively. Insert each timeDataThe system will automatically store records to the specified partition (tablespace) based on the value of the specified field.

Of course, we can also use the range distribution of two fields to partition as needed, such as partition by range (transaction_id, transaction_date). The value in the partition condition is also changed accordingly, please test it on your own.

1.1.2. Hash partition (hash partition)

Hash partitions are evenly distributed by specifying partition numbers.DataIs a partition type, because the size of these partitions is consistent by performing hash partitioning on the I/O device. For exampleDataThe transaction ID is hashed in the specified three tablespaces:

SQL> Create Table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number (8) Not null, 5 item_description varchar2 (300 ), 6 transaction_date date 7) 8 partition by hash (transaction_id) 9 ( 10 partition part_01 tablespace dinya_space01, 11 partition part_02 tablespace dinya_space02, 12 partition part_03 tablespace dinya_space03 13 ); Table created.

The table is successfully created and inserted.Data, The system inserts records into three partitions in a hash by transaction_id, which is three different tablespaces.

1.1.3. Composite Partition

Sometimes we need to partition by rangeDataThen hash is distributed in several tablespaces, so we need to use a composite partition. A composite partition uses a range partition first, and then uses a hash partition in each partition. For example, the records of material transactions are partitioned by time, andDataDivided into three sub-partitions:DataHashed data is stored in three specified tablespaces:

SQL> Create Table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number (8) 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 ( 11 partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')), 12 partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')), 13 partition part_03 values less than (maxvalue) 14 ); Table created.

In this example, range partitions are performed based on the transaction date, and records are hashed and stored in three tablespaces Based on the transaction ID.