Overview

What is Partitioning?

Table and Index data are physically stored in separate segments according to the partition key.

Advantages and Necessity of Partitioning

Method

Table Partitioning

Range Partition 

• Partitioning based on sorted values of a specific column
• Suitable for historical data tables.
• Usually based on date columns.
• Partitions must be defined in order.
• Automatic partition addition at fixed intervals is possible by defining intervals.

Hash Partition 

• Partitioning based on the hash value of the partition key
• Effective when the partition key column has evenly distributed data.
• It is recommended to partition by a power of 2.
• Distributes contention and improves parallel query performance.
• Effective for large transaction tables; partition addition is not possible.

List Partition 

• Partitioning by specific values of a specific column
• Effective when the distribution is similar and many SQL queries include conditions on that column as the partition key.
• Only a single column can be designated as the key.

Composite Partition 

• Data is first distributed by the partition key, and the final storage location (segment) is determined by the sub-partition key
• Range + Hash: Good for large transaction tables.
• Range + List: Mainly used when a very large historical table is range partitioned and each partition is further divided by business logic.

• Types of Composite Partitions Supported by Tibero

  	Range	Hash	List
  Range	O	O	O
  Hash	O	O	O
  List	O	O	O

Partition Management 

• Add Partition

  (Range) ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN (value) ;
  (List) ALTER TABLE table_name ADD PARTITION partition_name VALUES (value) ;

• Drop Partition

  ALTER TABLE table_name DROP PARTITION partition_name;

• Merge Partitions

  ALTER TABLE table_name
  MERGE PARTITIONS partition_name1, partition_name2 INTO PARTITION partition_name2 UPDATE indexes;

• Split Partition

  ALTER TABLE table_name
  SPLIT PARTITION partition_name2 AT split_key_value
  INTO (PARTITION partition_name1 tablespace tablespace_name1, 
  PARTITION partition_name2 tablespace tablespace_name2) ;

• Rename Partition

  ALTER TABLE table_name RENAME PARTITION old_partition_name TO new_partition_name;

• Change Partition Tablespace

   ALTER TABLE table_name MOVE PARTITION partition_name TABLESPACE tablespace_name;

• Truncate Partition

  ALTER TABLE table_name TRUNCATE PARTITION partition_name;

Related Dictionary Views

Partition Pruning

What is Pruning?

A function that analyzes SQL conditions at execution time and excludes segments that do not need to be read from the access target.

Static Partition Pruning

• When querying partition key columns with constant conditions
• Determined at query optimization time.
• The execution plan shows the partition numbers to be accessed in the PS (Partition Start) and PE (Partition End) columns.

Dynamic Partition Pruning

• When querying partition key columns with bind variables
• Determined at execution time.
• The execution plan shows ‘KEY’ in the PS and PE columns.

Index Partitioning

Global Partition Index

• When the table partition key and index partition key are different

Local Partition Index

• When the table partition key and index partition key are the same
• The DB automatically manages each table partition and index partition in a 1:1 correspondence
• When changes occur in the table partition, the index partition changes accordingly

In partitioned tables, local indexes are superior to global indexes in terms of performance, availability, and ease of management.

Index Partition Status

Prefixed

• When the first column of the index is the same as the index partition key
• Global indexes only support prefixed indexes.

Non-Prefixed

• When the first column of the index is different from the index partition key

When creating partitioned indexes, prefixed/non-prefixed is not defined explicitly; the DBMS checks and records it in the dictionary for management.

Related Dictionary Views