What is a partition table in oracle

In Oracle, a partition table decomposes a large table into multiple small, manageable partition tables. Each partition table is independent of each other and together constitute a complete table. Logically speaking, there is only one A table or an index, but physically this table or index may consist of several physical partitions; partitioned tables can be divided into range partitions, list partitions, hash partitions and combined partitions.

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

What is a partition table in oracle

(1) What is a partition

In Oracle database, in order to improve the processing of large tables/large indexes For manageability, maintainability and performance, Oracle has introduced a partitioned table mechanism that can decompose large tables/large indexes into multiple small, manageable partitions. Multiple partitions are relatively independent and have independent storage structures, which together constitute a complete table/index. Partitioning is transparent to the application, that is, for the application accessing the database, logically there is only one table or index (equivalent to the application still seeing only one table or index), but physically this table or index may consist of several Composed of physical partitions.

(2) When is partitioning required?

Oracle’s official website recommends using partitioned tables in the following situations:

1. When the amount of table data is greater than 2GB, it should be Consider using partitioning;

2. New data is added to the table used to store historical data in the latest partition.

(3) The difference between sub-table and partition table

In order to disperse the data of large tables/indexes, another way is to sub-table. Sub-table is to A large table is decomposed into multiple entity tables with independent storage space according to certain rules. For example, the customer table can be split into two tables, one for storing personal customers customer_person and the other for corporate customers customer_company. The sub-tables are completely independent and are logically multiple different tables, while the partition table is physically one table. Partitioned tables are transparent to applications and easy to manage and maintain, while sub-tables are not easy to manage and maintain.

(4) Type of partition table

• Range Partitioning

• List Partitioning(List Partitioning)

• Hash Partitioning(Hash Partitioning)

• Composite Partitioning(Composite Partitioning)

Basic partitioning refers to a partition table that only uses one layer of partitioning (range partitioning, list partitioning, hash partitioning).

If a partition table is partitioned into multiple levels, it is called combined partitioning.

Range partitioned table

(1) Partition key

Partitioning is to break down a very large table or index into multiple small, manageable part. Partitioned tables are partitioned by partition keys. The partition key determines which partition each row of data in the partitioned table flows to. Each row of data in a partitioned table is implicitly assigned to a partition based on the partition key.

(2) Range partitioning

Range partitioning is divided into partitions according to the partition key and according to the different range values ​​​​of the data rows falling in the partition key.

(3) Case 1 (the partition key is a single column)

CREATE TABLE time_range_sales
     ( prod_id        NUMBER(6)
       , cust_id        NUMBER
       , time_id        DATE
       , channel_id     CHAR(1)
       , promo_id       NUMBER(6)
       , quantity_sold  NUMBER(3)
       , amount_sold    NUMBER(10,2)
     )
    PARTITION BY RANGE (time_id)
    (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
     PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
     PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
     PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
    );

Recommended tutorial: "Oracle Video Tutorial"

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