The role of oracle table partitioning

The concept of table space and partition table

##Table space: (Recommended study:

Web front-end video tutorial)

is a collection of one or more data files. All data objects are stored in the specified table space, but the main storage is the table, so it is called table space

Partition table:

When the amount of data in the table continues to increase, the speed of querying data will slow down, and the performance of the application will decrease. At this time, 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 when querying the data, the entire table will not be scanned every time. Table

The specific role of table partitioning

Oracle’s table partitioning feature brings benefits to a variety of applications by improving manageability, performance, and availability. of great benefit. Generally, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common management tasks and is a key tool in building gigabyte data systems or ultra-high availability systems.

The partitioning function can further subdivide a table, index, or index-organized table into segments. The segments of these database objects are called partitions. Each partition has its own name and can select its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple segments, and these segments can be managed collectively or individually. This gives the database administrator considerable flexibility when managing partitioned objects. sex. However, from an application perspective, a partitioned table is identical to a non-partitioned table, and no modification is required when accessing a partitioned table using SQL DML commands.

When to use partition tables:

1. The size of the table exceeds 2GB.

2. The table contains historical data, and new data is added to the new partition.

Advantages and disadvantages of table partitioning

Table partitioning has the following advantages:

1. Improve query performance: queries on partitioned objects can only search for themselves Care about partitions to improve retrieval speed.

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

3. Easy maintenance: If a partition of the table fails, it is necessary to To repair data, only repair the partition;

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

Disadvantages:

Partition table related: There is no way to directly convert an existing table into a partition table. However, Oracle provides the function of online redefinition of tables.

(4). Several types and operation methods of table partitions

Range partitioning:

Range partitioning maps data to each partition based on the range. This range is your Determined by the partition key specified when creating the partition. This partitioning method is the most commonly used, and the partition key often uses a date. For example: you might partition your sales data by month.

When using range partitioning, please consider the following rules:

1. Each partition must have a VALUES LESS THEN clause, which specifies a value that is not included in the partition. upper limit value. Any records with a partition key value equal to or greater than this upper limit will be added to the next higher partition.

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

3. In the highest partition, MAXVALUE is defined. MAXVALUE represents an uncertain value. This value is higher than the value of any partition key in other partitions, and can also be understood as higher than the value of VALUE LESS THEN specified in any partition, including null values.

Example 1:

Suppose there is a CUSTOMER table with 200,000 rows of data. We partition this table by CUSTOMER_ID. Each partition stores 100,000 rows. We save each partition into a separate tablespace so that the data files can span multiple physical disks. The following is the code to create tables and partitions, as follows:

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
)

The above is the detailed content of The role of oracle table partitioning. For more information, please follow other related articles on the PHP Chinese website!