What is oracle table partitioning

In Oracle, table partitioning refers to physically storing the data in the table into multiple table spaces when the data in the table continues to increase, that is, partitioning the table; table partitioning can Tables, indexes, or index-organized tables are further subdivided into segments. Segments of these database objects are called partitions, which improve manageability, performance, and availability.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is Oracle table partitioning

Partitioned 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, it will not Scan the entire table every time.

The specific role of table partitioning

Oracle’s table partitioning function brings great benefits to various applications by improving manageability, performance and availability. benefit. In general, 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 tables, indexes or index-organized tables 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 a partition table, the official advice is:

• a. The size of the table exceeds 2GB.

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

Advantages and disadvantages of table partitioning

Advantages:

• a. Improve query performance: Yes When querying partition objects, you can only search the partitions you care about to improve retrieval speed.

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

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

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

Disadvantages:

Related to partition tables, there is no way to directly convert existing tables into partition tables. However, Oracle provides the function of online redefinition of tables.

Several types and operation methods of table partitions

1 Range partition (range) maxvalue

Range partition maps data to each location based on the range Partition, this range is determined by the partition key you specify 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:

a. 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.

b. 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.

c. If the range of some records cannot be predicted yet, you can create a maxvalue partition. All records that are not within the specified range will be stored in the partition where maxvalue is located.

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

----Create multiple test table spaces first

sys@ORCL>create tablespace test_ts01 datafile '/home/oracle/test_01.dbf' size 32m extent management local autoallocate;
Tablespace created.
sys@ORCL>create tablespace test_ts02 datafile '/home/oracle/test_02.dbf' size 32m extent management local autoallocate;
Tablespace created.
sys@ORCL>create tablespace test_ts03 datafile '/home/oracle/test_03.dbf' size 32m extent management local autoallocate;
Tablespace created.

----Create test partition table

create table test
(        id number not null,
         first_name varchar2(30) not null,
         last_name varchar2(30) not null,
         phone varchar2(30) not null,
         email varchar2(80),
         status char(1),
         constraint test_id primary key (id)
)
partition by range (id)
(        partition test_part1 values less than (100000) tablespace test_ts01,
         partition test_part2 values less than (200000) tablespace test_ts02,
         partition test_part3 values less than (maxvalue) tablespace test_ts03
);

Recommended tutorial: "Oracle Video Tutorial"

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