On Oracle Database partition table

Oracle database Partitioning is an important means and method of Oracle database performance optimization, before, only heard the name of the partition, but never used, recently a simple study, summarized as follows, the wrong place, but also hope that friends more guidance, communication!

1. Table space and the concept of partitioned tables
2. The specific role of table partitioning
3. Advantages and disadvantages of table partitioning
4. Several types of table partitioning and how to operate them
5. Maintenance Operations on table partitioning.

(1) The concept of table space and partitioned tables
Table Space:
is a collection of one or more data files, all of which are stored in the specified table space, but are primarily tables, so called table spaces.
partition Table:
When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files) so that querying the data does not always scan the entire table.

(2) The specific role of table partitioning

Oracle 's table partitioning capabilities bring great benefits to a wide variety of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabytes of data systems or ultra-high availability systems.

The partitioning feature can further subdivide a table, index, or index organization table into segments, where the segments of these database objects are called partitions. Each partition has its own name, and you can choose your own storage features. From the database administrator's point of view, a partitioned object has multiple segments that can be collectively managed or managed separately, which gives the database manager considerable flexibility in managing the objects after the partition. However, from the application's point of view, the partitioned table is exactly the same as the non-partitioned table, and no modifications are required to access the partitioned table using the SQL DML command.

When to use partitioned tables:
1, the size of the table is more than 2GB.
2, the table contains historical data, new data is added to the new partition.

(3.) Advantages and disadvantages of table partitioning

Table partitioning has the following advantages:

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

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

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

4) Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.

Disadvantages:
Partition Table Related: Existing tables have no methods that can be converted directly into partitioned tables. However, Oracle provides the ability to redefine tables online.

(4) Several types of table partitioning and how to operate them

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

ü Range Partitioning

Ühash partition (hash partition)

ü Composite Partitioning

1. Detailed description of scope partition

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, depending on the number of partitions , depending on the time and so on to partition, you can also use the two fields together to partition the specific needs of the project needs and other reasons. According to the serial number, such as less than 2000000 placed in the part01, 2000000~4000000 placed in the part02 ...

CREATE TABLE AAA (

ID number primary Key,

Indate date NOT NULL

) partition by Range (Indate) (

Partition part_01 values less than (to_date (' 2006-01-01 ', ' yyyy-mm-dd ') tablespace space01,

Partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ') tablespace space02,

Partition part_03 values less than (MaxValue) tablespace space03

);

Space01\ space02\ space03 is the three table space created, which is equivalent to dividing a large table into 3 different table space partitions.

2, hash partition (hash partition) detailed description

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. This is to name only the partition names so that the data is distributed evenly.

CREATE TABLE AAA (

ID number primary Key,

Indate date NOT NULL

) partition by Range (indate) Subpartition by hash (ID) (

Partition part_01 tablespace space01,

Partition part_02 tablespace SPACE02,

Partition part_03 tablespace space03
);

3. Detailed description of compound partition

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 uses a range partition first and then uses it within each partition

A partitioning method for hash partitions.

Partition by Range (indate) Subpartition by hash (ID)

Subpartitions 3 store in (SPACE01, SPACE02, SPACE03) (

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)

);

(5.) Some maintenance actions related to table partitioning:

1. Insert Record:

INSERT into AAA values (1, sysdate);

INSERT into AAA values (2, to_date (' 2006-01-01 ', ' yyyy-mm-dd '));

INSERT into AAA values (3, to_date (' 2011-01-01 ', ' yyyy-mm-dd '));

2, query partition table records: SELECT * from AAA partition (PART_01);

3. Update the partition table record: Update AAA partition (PART_01) t set indate= ' 2015-01-30 ' where t.id=1;

However, the data is not updated when the partition is specified when the update is made, and the record is not in the partition according to the query.

4. Delete partition table record: Delete from AAA partition (part_02) t where t.id=4;

If a partition is specified and the data in the condition is not in that partition, no data is deleted.

5. Add a partition:

ALTER TABLE AAA add partition part_04 values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')) tablespace Dinya_spa ce03;

When adding 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.

6. Merge a partition:

ALTER TABLE AAA merge partitions part_01,part_02 into partition part_02;

If the merged partition is set to part_01 when merging, the system will prompt ORA-14275 cannot reuse lower-bound partition as resulting

Partition error.

7. Delete partition: ALTER TABLE AAA drop partition part_01; 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.

(6.) Building an Index

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. Establishment of the local index partition:

Create INDEX idx_t on AAA (ID) Local (

Partition idx_1 tablespace space01,

Partition Idx_2 tablespace SPACE02,

Partition Idx_3 tablespace space03

);

2. The global clause allows the range value of the index field to be specified, which is the range value of the indexed fields:

Create INDEX idx_t on AAA (ID) global partition by range (ID) (

Partition idx_1 values less than (+) tablespace space01,

Partition idx_2 values less than (10000) tablespace SPACE02,

Partition Idx_3 values less than (MaxValue) tablespace space03

);

Of course, you can index an entire table without specifying an index partition name:

Create INDEX idx_t on AAA (ID);

　　

Summarize:
It should be noted that the above partition table transaction operation when the partition is specified, because the partition system is specified in the execution of only the records of the partition, improve the processing speed of the data. 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 the partitioning may have some impact on the index of the partition while maintaining it, it may be necessary to rebuild the index after maintenance, and refer to the documentation in the Partition Table index section for related content.

On Oracle Database partition table