Each partition is an independent object with its own name and optionally its own storage characteristics.

Benefits include:

Increased availability： The query optimizer automatically removes unreferenced partitions from the query plan.
Easier administration of schema objects: A partitioned object has pieces that can be managed either collectively or individually.
Reduced contention for shared resources in OLTP systems.
Enhanced query performance in data warehouses

Partition Characteristics

Each partition of a table or index must have the same logical attributes, such as column names, data types, and constraints.However, each partition can have separate physical attributes, such as the tablespace to which it belongs.

Partition Key

A set of one or more columns that determines the partition in which each row in a partitioned table should go.

Partitioning Strategies

Range Partitioning: The database maps rows to partitions based on ranges of values of the partitioning key.
List Partitioning: The database uses a list of discrete values as the partition key for each partition.
Hash Partitioning: The database maps rows to partitions based on a hashing algorithm that the database applies to the user-specified partitioning key.

For example:

1) CREATE TABLE time_range_sales (prod_id NUMBER(6)

, cust_id NUMBER

, time_id DATE

.......)

PARTITION BY RANGE (time_id)

( ...... );

2) CREATE TABLE list_sales ( prod_id NUMBER(6)

, cust_id NUMBER

, time_id DATE

, channel_id CHAR(1)

...... )

PARTITION BY LIST (channel_id)

( ...... );

3) CREATE TABLE hash_sales ( prod_id NUMBER(6)

, cust_id NUMBER

...... )

PARTITION BY HASH (prod_id)

PARTITIONS 2;

Partitioned Tables

A partitioned table consists of one or more partitions managed individually. And a nonpartitioned table cannot have partitions added to it.

A partitioned table is made up of one or more table partition segments. If you create a partitioned table named hash_products, then no table segment is allocated for this table.

Partitioned Indexes

ITPub博客

NOTES on concepts ---Partitions

最新文章