Database optimization-oracle table partition creation and classification

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 querying the data may not require scanning the entire table every time.

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. 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 or the number of rows in the table may exceed 5 million.

2, different data needs to be processed in different batches.

Advantages and disadvantages of table partitioning

Table Partitioning Benefits:

1, Improve performance: The query of the partition object can only search for the partition you care about, improve the speed of use.

2. Enhanced usability: If one partition of the table fails, the data of the table in other partitions is still available;

3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;

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

Table Partitioning Disadvantages:
1. If used improperly, it will degrade performance, such as no read operation of the specified partition;

2. In important data occasions, a partition error, the service (transaction) must be stopped, such as payment system;

3. After using the partition, the same data, the backup data is much slower than before the partition, the volume is much larger;

4. Reduced throughput if used improperly

Several types of table partitioning and how to operate them

One, RANGE partition:
A range partition maps data to each partition based on its scope, which is determined by the partition key you specify when you create the partition. This partitioning method is most commonly used, and the partitioning key often takes the date. For example, you might partition sales data by month.

When using range partitioning, consider the following rules:

1. Each partition must have a values less then clause that specifies an upper value that is not included in the partition. Any record of the partition key that is equal to or greater than this upper value is added to the next higher partition.

2. All partitions, except the first one, will have an implicit lower value, which is the upper limit of the previous partition of this partition.

3. In the highest partition, MaxValue is defined. The MaxValue represents an indeterminate value. This value is higher than the value of any partition key in the other partition, and it can be understood to be higher than the value less or less that is specified in any partition, including null values.

Example one:

Suppose there is a tel table with 20 million rows in the table, we partition the table with IDs, each partition stores 10 million rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows:

CREATE TABLE TEL
(
ID number not NULL PRIMARY KEY,
PHONE VARCHAR2 (+) not NULL
)
PARTITION by RANGE (ID)
(
PARTITION tel_part1 VALUES less THAN (100000000) tablespace tel_ts01,
PARTITION tel_part2 VALUES less THAN (200000000) TABLESPACETEL_TS02
)

two . list partition:

This partition is characterized by a column with only a few values, based on which we can take the list partition. For example, our Tel watch, which is mainly used by two places in Shanghai and Beijing,

CREATE TABLE TEL

(

ID INTEGER NOT NULL,

Prov_region_code INTEGER,

City_region_code INTEGER,

Sub_city_region_code INTEGER,

PHONE VARCHAR2 (16)

)

Partition by list (Prov_region_code)

(

Partition PART11 values (110000),

Partition PART31 values (310000),

Partition PARTDFT values (default)

);

This partition is created on the Prov_region_code field.

Partition part11values (110000) said Beijing;

Partition part31values (310000) said Shanghai;

Partition partdftvalues (default), data from other places are placed in this default partition.

110000 and 310000 are provincial-level codes for China's national planning.

three . Hash Partition:
This type of partitioning uses a hashing algorithm on column values to determine which partition the rows are placed in. Hash partitioning is recommended when the value of the column does not have an appropriate condition.

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.

Example one:

CREATE TABLE Hash_tel
(
COL Number (8),
INF VARCHAR2 (100)
)
PARTITION by HASH (COL)
(
PARTITION PART01 tablespace hash_ts01,
PARTITION PART02 tablespace HASH_TS02,
PARTITION PART03 tablespace Hash_ts03
);

Hash partition The main mechanism is based on the hash algorithm to calculate the specific record should be inserted into which partition, hash algorithm is the most important hash function, Oracle if you want to use the hash partition, only specify the number of partitions. It is recommended that the number of partitions be 2 N, which makes the data distribution more evenly between partitions.

The efficiency of hash partition writing is very good, but the efficiency of reading is not optimistic, so pay attention to the business scenario.

Four . Composite Partitioning
A composite partition is a partition, embedded in another partition, the following example is based on a range partition and a list partition, the table first by a column for the scope partition, and then by a column for the list partition, partition is called sub-partition.

CREATE Tablesales

(

ID INTEGER,

Sales_datedate,

STATUSVARCHAR2 (20)

)

PARTITION Byrange (sales_date) subpartition by LIST (STATUS)

(

PARTITION P1 VALUES LessThan (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')

(

Subpartition p1sub1 VALUES (' ACTIVE '),

Subpartition p1sub2 VALUES (' INACTIVE ')),

PARTITION P2 VALUES Less THAN (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')

(

Subpartition p2sub1 VALUES (' ACTIVE '),

Subpartition p2sub2 VALUES (' INACTIVE ')

)

)

Use the time must be cautious, the more complex objects, to grasp the more difficult.

Some special usage scenarios:

1. Use table partition logging, general log retention 1 years, more than one year of log deletion, if you want to delete data and free space, if you do not do special processing, the general need to stop the application; this time you can use the table partition, a year to create a partition, and then delete the expired partition can be;

ALTER TABLE SALES TRUNCATE PARTITION P2;

If you need to use partitioning for an important project, it's best to read some professional books. For example, "oracle10g SQL and PL/SQL Programming Guide". It's OK to look at the harvest.

Database optimization-oracle table partition creation and classification