Oracle Partition Table!

creation and operation of Oracle database partitioned tables

Summary: In a large number of business data processing projects, you can consider using 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 the development of storage media and data processing technology is also fast, but still does not meet the needs of users, in order to make a large number of users of data in read and write operations and queries faster, Oracle provides the table and index partitioning technology to improve the performance of large application systems.

Advantages of using partitioning:

• Enhanced usability: If one partition of the table fails, the data for the table on the other partitions is still available;

• Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;

• Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance;

• Improved query performance: Queries on partitioned objects can search only the partitions you care about and improve the retrieval speed.

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

• Range Zoning (range)

· hash partition (hash partition)

• Composite partitioning (list)

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

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. Creation of partitioned Tables

1.1.1. Range Partitioning

A range partition is the partitioning of a range of values in a data table, depending on the extent of a value, deciding which partition to store the data on. For example, according to the sequence number partition, according to the Business record creation date partition and so on.

Requirements Description: There is an item Trading table, table name: Material_transactions. The table may have a tens number of data records in the future. The partition table is required when the table is under construction. At this time we can use the ordinal partition three zones, each region 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 create a table based on the ordinal partition of the transaction:

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 build table succeeds, according to the serial number of the transaction, the record of the transaction ID under 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, and records with transaction IDs above 60 million are stored in the third tablespace dinya_space03, with the partition name 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(’2006-01-01’,’yyyy-mm-dd’))
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.
This allows us to create separate partitioned tables that are partitioned by trade sequence numbers and trading dates. Each time the data is inserted, the system automatically stores the record 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 partitioning condition are also changed accordingly, please test the reader by 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 the partitions are identical in size by hashing 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) 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.
When the build table succeeds, the data is inserted, and the record is hashed into three partitions by transaction_id, which is three different table spaces.

1.1.3. Composite partitioning

Sometimes we need to partition the data in each partition, and then hash it out in a few table spaces, so we're going to 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 partitioning records for item transactions by time, then dividing the data in each partition by three sub-partitions, and storing the data 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(’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.
In this example, the range is partitioned based on the transaction date, and the records are hashed in three tablespace based on the ID of the transaction.
　　 1.2. Partitioning table Operations

Learn more about the table-building methods for the three partitioning tables below, using the actual data and testing the data records of partitioned tables for date-based range partitioning.

1.2.1. Inserting records:

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 results of the above table, data for 2006 years ago will be stored on the first partition part_01, and the 2006 to 2010 transaction data will be stored on the second partition part_02, and the records after 2010 are stored on the third partition part_03.

1.2.2. Querying partition Table records:

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-23
SQL>
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-30
SQL>
As can be seen from the results of the query, the inserted data has been stored in different partitions based on the trading time range. This is the query that specifies the partition, and of course you can not specify the partition, directly execute the SELECT * from Dinya_test query all records.

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

1.2.3. Update the records for the partitioned 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>
This updates the Item_description field in the record of the transaction id=1 in the first partition to "DESK", and you can see that a record has been successfully updated. However, if you specify a partition when updating, and the data is not updated according to the record of the query, 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 the record is updated in the first partition, but the condition restricts the transaction ID to 6, while querying the full table, the record with the transaction ID 6 is in the third partition so that the statement will not update the record.

1.2.4. Deleting 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 above example deletes a record with the transaction ID 4 in the second partition part_02, and the same as the update data, if a partition is specified and the data in the condition is not in that partition, no data will be deleted.

　　 1.3. Use of partitioned table indexes:

Partitioned tables can be indexed as well as general tables, 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 guaranteed.

1.3.1. Establishment of a 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>
Looking at the execution plan of the query, as can be seen from the following execution plan, the system has already 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 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. Establishing a global index partition

The global clause allows you to specify the range value for the index field, which is the range value of the indexed fields:

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 is indexed and, of course, you can index the entire table without specifying an 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 ready for use based on 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 gets
10 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 partitioned tables:

After understanding the establishment of partitioned tables, the establishment of indexes, the use of tables and indexes, it is often used to maintain and manage partitions. Daily maintenance and management includes adding a partition, merging a partition and deleting partitions, and so on. The following example illustrates the general operation of adding, merging, and deleting partitions with a range partition:

1.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_spa
ce03;
Table altered.
SQL>
When you add a partition, the condition of the added partition must be greater than the maximum value of the existing partition, otherwise the system will prompt ORA-14074 partition bound must collate higher than that's the last partition error.

1.4.2. Merging a 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 of the PART_01 partition and the part_02 partition are merged, the combined partition is part_02, if the merged partition when the merge is designated as PART_01, the system will prompt ORA-14275 cannot reuse Lower-bound partition as resulting partition error.

1.4.3. Deleting partitions:

SQL> alter table dinya_test drop partition part_01;
Table altered.
SQL>
After you delete a partition of a partitioned table, the data in the table is queried to show that the data in the partition is all lost, so be cautious when performing a delete partition action, ensuring that data is backed up before execution, or merging partitions.

　　 1.5. Summary:

It should be noted that, in the case of the name partition table transaction operations, all specify the partition, because the partition is specified, the system executes only the records of the partition, improve the speed of data processing. Do not specify partitions to manipulate the data directly is also possible. The use of indexes on partitioned tables and multi-indexes is the same as for non-partitioned tables. In addition, since it is possible to have some impact on the partitioning index while maintaining the partition, it may be necessary to rebuild the index after maintenance, refer to the documentation in the Partition Table index section for related content

Oracle Partition Table!