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:
1. Enhance usability: If a partition of a table fails, the data in other partitions is still available;
2, Maintenance Convenience: If the table of a section of the fault, need to repair the data, only repair the partition can;
3, balanced I/O: You can map different partitions to disk to balance I/O, improve the performance of the entire system;
4, Improve query performance: The query on the partition object can only search their own care, improve the speed of retrieval.
The Oracle database provides three ways to partition a table or index:
1. Range Zoning
2. Hash partition (hash partition)
3. Composite partition
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.
1 Partition Table creation: 1.1 Range partition
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:
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.
Sql>
The table succeeds, 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, the record between 30 million and 60 million is stored in the second tablespace: DINYA_SPACE02, the partition name is: Par_ 02, while the transaction ID in more than 60 million of the records stored in the third tablespace dinya_space03, the partition name is par_03.
To create a table based on the transaction date:
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.
Sql>
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.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:
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.
Sql>
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.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:
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.
Sql>
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.
2 Partitioned Table Operations
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.
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 ', ' 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.
2.2 Query partition Table records:
Sql> SELECT * from Dinya_test partition (PART_01);
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.
2.3 Update the records 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 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:
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.
2.4 Delete partition Table records:
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.
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.
3.1 The establishment of local index partitions:
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:
Sql> SELECT * from Dinya_test partition (part_01) t where t.item_id=12;
3.2 The establishment of the global index partition.
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:
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:
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:
Sql> SELECT * from Dinya_test t where t.item_id=12;
4 Partition Table Maintenance:
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:
4.1 Add one partition:
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.
4.2 Merge One partition:
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.
4.3 Delete partitions:
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.
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 the partitioning may have a certain impact on the partition's index when it is maintained, you may need to rebuild the index after maintenance, and refer to the document in the index section of the partition table.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
