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>
The success of the table, according to the serial number of the transaction, the transaction ID 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 first Two tablespaces: Dinya_Space02, the partition name is: PAR_02, and the transaction ID is stored in the third table space Dinya_Space03 in the third table space Dinya_Space03. The partition is named PAR_03. According to the trading date partitions:
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 Hash Partition (Hash 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 measures were successful, and the data was inserted, and the system will be inserted into three partitions in transaction_id, which is also three different tablespaces.
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 Columns stored in three designated tablespaces: SQL> Create Table Dinya_test 2 (3 Transaction_id Number Primary Key, 4 item_id number (8) Not null, 5 item_description varcha2 (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 ( '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. 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>
According to the above-mentioned metrics, the data before 2006 will be stored on the first partition Part_01, while the transaction data in 2006 to 2010 will be stored on the second partition Part_02, and the record after 2010 is stored in 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>
As can be seen from the results of the query, 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 Updating the logistics 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> Specify update record in the first partition, but conditions In the middle limit of the transaction ID is 6, the full table, the transaction ID is recorded in the third partition so that the statement will not update the record.
2.4 Delete Partition Table Record:
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 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 Establishment of 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 Processedsql>
3.2 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>
In this example, the Item_ID field of the table establishes an index partition, and of course, it is possible to 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 the partition table:
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') TableSpace Dinya_Space03; Table Altered.SQL> When adding a partition, increased partition The conditions must be greater than the maximum of existing partitions, otherwise ORA-14074 Partition Bound Must Collate Higher Than 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 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.
5 summary:
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.
