Manage Partitions

Use partitioning to divide large tables and indexes into smaller, more manageable pieces.

Partitions

Partitions are database objects that have unique IDs and can be managed independently. Each partition can reside on a separate segment.

Horizontal partitioning is supported, which means you can distribute a selection of table rows among storage devices. Assign individual table or index rows to a partition according to a partitioning strategy. By default, every table and index is created on a single, round-robin partition. You can also choose a semantics-based strategy that assigns rows to partitions.

Semantics-based partitioning is a separately licensed feature.

Hash Partitioning

With hash partitioning, a hash function is used to specify the partition assignment for each row. You select the partitioning key columns, but SAP ASE chooses the hash function that controls the partition assignment. Hash partitioning is a good choice for:

Large tables with many partitions, particularly in decision-support environments
Efficient equality searches on hash key columns
Data that has no particular order, for example, alphanumeric product code keys

If you choose an appropriate partition key, hash partitioning distributes data evenly across all partitions. However, if you choose an inappropriate key, for example, a key that has the same value for many rows—the result may be skewed data, with an unbalanced distribution of rows among the partitions.

Range Partitioning

Rows in a range-partitioned table or index are distributed among partitions according to values in the partitioning key columns. The partitioning column values of each row are compared with a set of upper and lower bounds that determine the partition to which the row belongs.

Every partition has an inclusive upper bound and every partition except the first has a noninclusive lower bound.

Range partitioning is particularly useful for high-performance applications in both OLTP and decision-support environments. Select ranges carefully so that rows are assigned equally to all partitions—knowledge of the data distribution of the partition key columns is crucial to balancing the load evenly among the partitions. Range partitions are ordered; that is, each succeeding partition must have a higher bound than the previous partition.

List Partitioning

As with range partitioning, list partitioning distributes rows semantically; that is, according to the actual value in the partitioning key column. A list partition has only one key column. The value in the partitioning key column is compared with sets of user-supplied values to determine the partition to which each row belongs. The partition key must match exactly one of the values specified for a partition.

The value list for each partition must contain at least one value, and value lists must be unique across all partitions. You can specify as many as 250 values in each list partition. List partitions are not ordered.

Round-Robin Partitioning

In round-robin partitioning, partitioning criteria is not used. Round-robin-partitioned tables have no partition key. Rows are assigned in a round-robin manner to each partition so that each partition contains a more or less equal number of rows and load balancing is achieved. Because there is no partition key, rows are distributed randomly across all partitions.

Partition Locking
Partition locking improves concurrency and data availability.
Enabling Semantic-based Partitioning
Enable semantic-based partitions to use hash, list, or range partition strategies.
Using a Hash Partition
Create a new partition or change an existing partition using a strategy of a system-generated hashing function.
Using a Range Partition
Create a new partition or change an existing partition according to whether one or more values in a row fall within a range of predefined values for the partition.
Using a List Partition
Create a new partition or change an existing partition according to whether one value in the row matches one of a set of predefined values unique for each partition.
Using a Round-Robin Partition
Create a new partition or change an existing partition using the round-robin strategy so that each partition contains an approximately equal number of rows.
Splitting a Partition
Splitting partitions can improve performance on partitions that include frequent queries and updates.
Merging Partitions
Merging partitions consolidates data from partitions that are accessed infrequently into a single partition. You can merge only list and range partitions.
Moving a Partition
Table owners, database owners, or system administrators can move partitions to another segment.
Deleting a Partition
Drop a user or proxy table partition.

Parent topic: Tables