Use of the partition table in Oracle
Dinya (dinya20@tom.com)
Summary: In a large number of business data processing, you can consider using partition tables to improve the performance of the application and facilitate data management. This article details the use of partition tables.
text:
In large enterprise applications or enterprise-class database applications, the amount of data to process can usually reach tens to hundreds of GB, and some can even go to TB. Although the development of storage media and data processing technology is also very fast, but still does not meet the needs of users, in order to enable users' large amount of data in read and write operations and queries, Oracle provides the technical partitioning of tables and indexes To improve the performance of large application systems.
Use the advantages of the partition:
1, enhance availability: If a partition of the table fails, the data in other partitions is still available;
2. Convenient maintenance: If there is a fault in a partition of the table, you need to fix the data, which is only fixed;
3. Balance I / O: You can map different partitions to disk to balance I / O, improve the entire system performance;
4. Improve Query Performance: Query for partition objects can only search for some partitions care, improve retrieval speed.
There are three partition methods for the Oracle database to provide tables or indexes:
1, range partition
2, haveh partition (havehed partition)
3, composite partition
These three partition methods will be described below to illustrate the use of the partition table in the way in the example. For the convenience of testing, we first build three tablespaces.
Create TableSpace Dinya_Space01DataFile '/Test/demo/Orcle/demodata/dinya01.dnf' size 50m
Create TableSpace Dinya_Space01DataFile '/Test/demo/Orcle/demodata/dinya02.dnf' size 50M
Create TableSpace Dinya_Space01DataFile '/Test/demo/Orcle/demodata/dinya03.dnf' size 50m
1. Creation of the partition table:
1.1, range partition
The range partition is partitioned in the range of a value in the data table, and decides which partition that is stored in the range of a certain value. If the partition is partitioned according to the serial number partition, the partition is performed according to the date of creation of business records.
Demand Description: There is a material trading form, a table name: Material_Transactions. The table may have thousands of data records in the future. The partition table is required to use the table. At this time we can use the serial partition three zones, and each zone is expected to store 30 million data, or the date partition can be used, such as the data stored every five years.
Sequence number partitioning table according to 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.SQL> table successfully built, according to the transaction number, transaction ID in The recordings of 30 million will be stored in the first table space Dinya_Space01, the partition name: PAR_01, recorded between 300 million to 60,000 records in the second table space: Dinya_Space02, the partition name: PAR_02, and the transaction ID is stored in the third table space Dinya_Space03 in the third table space, and the partition is named PAR_03.
According to the Date of Trading Date:
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 ('2006-01-01')) TABLESPACE DINYA_SPACE01, 11 Partition Part_02 Values Less Than (To_Date ('2010-01-01', 'YYYY-MM-DD')) TableSpace Dinya_Space02, 12 Partition Part_03 Values Less Than (MaxValue) TableSpace Dinya_Space03 13); Table Created.sql>
In this way, we have built a partition table that partitions the trading serial number and the date of trading. When data is inserted, the system will automatically store the recorded partition (table space) according to the value of the specified field.
Of course, we can also use the range distribution of two fields to partition, such as Partition by Range (Transaction_ID, Transaction_Date), and the values in the partition conditions are also changed, please test it by yourself.
1.2, haveh partition (havehed partition)
The hash partition is a partition type that is uniformly distributed by specifying partition numbers because the partition is consistent with the sized partition on the I / O device. If the data of the material trading list is stored in the specified three table spaces according to the transaction ID:
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.SQL> The metrics are successful, inserted into the data, the system will insert the record has been inserted into three partitions, which is three different tables here. Space.
1.3, composite partition
Sometimes we need to be partitioned according to the range partition, and the data in each partition is distributed in several tablespaces so that we should use the composite partition. The composite partition is first to use the range partition, and then use a partition method of the hash partition in each partition, such as the record of the material transaction by time partition, then three sub-partitions in each partition, and scatter data Column 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_Space02, Dinya_Space02, Dinya_Space03) 10 (11 Partition Part_01 VALUES LESS THAN (To_Date ('2006-01-01', 'YYYY-MM-DD')), 12 Partition Part_02 Values Less Than (To_Date ('2010- 01-01 ',' YYYY-MM-DD ')), 13 Partition Part_03 Values Less Than (MaxValue) 14); Table Created.SQL>
In this example, the range partition is performed according to the trading date, and then stored in three tablespaces in three tablespaces based on the ID of the transaction.
2, partition table operation
The above understanding of the three partition tables will be understood, and the actual data will be used to test the data records of the partition table for the range of data on the date.
2.1, insert record:
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'); 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> Press the above-mentioned metrics, 2006 data will be stored on the first partition Part_01, while the transaction data in 2006 to 2010 will be stored in the second partition part_02 On, records after 2010 are stored on the third partition Part_03.
2.2, query partition table record:
SQL> Select * from dinya_test partition (part_01); transaction_id item_id item_description transaction_date --------------------------------- -------------------------------------------- 1 12 Books 2005- 1-14 14:19: 2 12 Books 2005-2-13 14: 19: SQL> SQL> SELECT * FROM DINYA_TEST Partition (Part_02); Transaction_ID item_id item_description transaction_date -------------- -------------------------------------------------- ---------------- 3 12 Books 2006-5-30 4 12 Books 2007-6-23SQL> SQL> Select * from dinya_test partition (part_03); Transaction_ID item_id item_description transaction_date-- -------------------------------------------------- ---------------------------- 5 12 Books 2011-2-26 6 12 Books 2011-4-30sql> From the results of the query, it can be seen that the inserted data has been stored in different partitions based on the transaction time range. Here is the query specified in the partition, of course, may not specify a partition, and perform SELECT * from Dinya_Test query all records directly. When the amount of data also retrieved is large, the specified partition will greatly improve the retrieval speed.
2.3, update the record of the partition table:
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 in the first partition is updated to "Desk", and a record has been successfully updated. But when the update is updated, the data will not be updated according to the record of the query, and the data will not be updated, please see the example below:
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 the update record in the first partition is specified, but the transaction ID is 6, but the full-query full menu, the transaction ID is recorded in the third partition so that the statement will not update the record. 2.4, delete partition table records:
SQL> delete from dinya_test partition (part_02) T where t.transaction_id = 4;
1 row deleded.
SQL> commit;
COMMIT COMPLETE.
SQL>
The above example deletes a record of the transaction record ID 4 in the second partition Part_02, and the update data is the same. If the partition is specified, the data in the condition is not in the partition, any data will not be deleted.
3, the use of partition table index:
The partition table and general table can establish an index, and the partition table can create local indexes and global indexes. Many transactions appear in the partition and to ensure uniqueness of data records in all partitions.
3.1, the establishment of the local index partition:
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>
Look at the execution plan of the query, you can see from the execution plan below, the system has used the index:
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 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 Get
4 consistent gets
0 Physical READS
0 redo size
334 BYTES SENT VIA SQL * NET to Client
309 Bytes Received Via SQL * NET from Cliant
2 SQL * NET ROUNDTRIPS TO / FROM Client
1 Sorts (Memory)
0 Sorts (Disk)
2 rows proped
SQL>
3.2, the establishment of the global index partition.
The global index is established when the Global clause allows the specified range value of the index, which is the range value of the index field:
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> This example establishes an index partition, of course, can also establish an index of the entire table without specifying the index partition name, such as:
SQL> CREATE INDEX DINYA_IDX_T ON DINYA_TEST (Item_ID); Index Created.SQL>
Similarly, you can see that the index can be used by the overall index according to the execution plan:
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 Get
10 consistent gets
0 Physical READS
0 redo size
420 BYTES SENT VIA SQL * NET to Client
309 Bytes Received Via SQL * NET from Cliant
2 SQL * NET ROUNDTRIPS TO / FROM Client
3 sorts (memory)
0 Sorts (Disk)
5 rows proped
SQL>
4. Maintenance of partition tables:
Understand the establishment of the partition table, the establishment of the index, the use of the table, and index, and the partition is often maintained and managed in the application. The content of daily maintenance and management includes: increasing a partition, combining a partition and deleting partition, and more. The following is a general operation of increasing, merging, and deleting partitions as an example:
4.1, add a partition:
SQL> ALTER TABLE DINYA_TEST 2 Add Partition Part_04 Values Less Than (To_date ('2012-01-01', 'YYYY-MM-DD')) TableSpace Dinya_Space03; Table Altered.SQL>
When adding a partition, the increasing partition must be greater than the maximum of the existing partition, otherwise the system will prompt ORA-14074 Partition Bound Must Collate Higher That of The Last Partition error. 4.2, merge a partition:
SQL> ALTER TABLE DINYA_TEST MERGE Partitions Part_01, Part_02 INTO Partition Part_02; Table Altered.SQL>
In this case, the part_01 partition and Part_02 partition of the original table are combined. The merged partition is part_02, and if the merged partition is set to Part_01 when the merge is, the system will prompt ORA-14275 Cannot Reuse. Lower-Bound Partition As Resulting Partition error.
4.3, delete the partition:
SQL> ALTER TABLE DINYA_TEST DROP Partition Part_01; Table Altered.sql>
After deleting a partition of the partition table, query the data of the table, displayed, the data in the partition is all lost, so it is necessary to perform the delete partition action, make sure the data is later executed, or combine the partition.
to sum up:
It should be noted that the partition is specified in the example of the partition table transaction operation, because the partition is specified, the system only operates the record of the partition and improves the speed of data processing. Do not specify partition direct operation data. The use of indexes and multi-indexes in partition tables is the same as the non-partition table. In addition, since the index of the partition may have a certain impact on the index of the partition, it may be necessary to reconstruct the index after maintenance, please refer to the documentation of the partition table index section.
