Partition Tables and Indexes

Use partitioning to manage large tables and indexes by dividing them into smaller, more manageable pieces. Partitions, like a large-scale index, provide faster and easier access to data.

Each partition can reside on a separate segment. Partitions are database objects and can be managed independently. You can, for example, load data and create indexes at the partition level. Yet partitions are transparent to the end user, who can select, insert, and delete data using the same DML commands whether the table is partitioned or not.

SAP ASE supports horizontal partitioning, in which a selection of table rows can be distributed among disk devices. Individual table or index rows are assigned to a partition according to a partitioning strategy.

Partitioning is the basis for parallel processing, which can significantly improve performance.

Note: Semantics-based partitioning is licensed separately. To enable semantic partitioning at a licensed site, set the value of the enable semantic partitioning configuration parameter to 1. See, Setting Configuration Parameters, in the System Administration Guide: Volume 1.

Partitioning:

Improves scalability.
Improves performance – concurrent multiple I/O on different partitions, and multiple threads on multiple CPUs working concurrently on multiple partitions.
Provides faster response time.
Provides partition transparency to applications.
Supports very large database (VLDB) – concurrent scanning of multiple partitions of very large tables.
Provides range partitioning to manage historical data.

Note: By default, SAP ASE creates tables with a single partition and uses a round-robin partitioning strategy. These tables are described as “unpartitioned” to distinguish between tables created or modified without partitioning syntax (the default) and those created with partitioning syntax.

Data Partitions

A data partition is an independent database object with a unique partition ID. It is a subset of a table, and shares the column definitions and referential and integrity constraints of the base table.

To maximize I/O parallelism, SAP recommends that you bind each partition to a different segment, and bind each segment to a different storage device.

Partition Keys

Each semantically partitioned table has a partition key that determines how individual data rows are distributed to different partitions. The partition key may consist of a single partition-key column or multiple key columns. The values in the key columns determine the actual partition distribution.

Range- and hash-partitioned tables can have as many as 31 key columns in the partition key. List partitions can have one key column in the partition key. Round-robin partitioned tables do not have a partition key.

You can specify partitioning-key columns of any type except:

text, image, and unitext
bit
Java classes
Computed columns

You can partition tables containing columns of these datatypes, but the partitioning key columns must be of supported datatypes.

Index Partitions

Indexes, like tables, can be partitioned. You can create local as well as global indexes.

An index partition is an independent database object identified with a unique combination of index ID and partition ID; it is a subset of an index, and resides on a segment or other storage device.

SAP ASE supports local and global indexes.

A local index – spans data in exactly one data partition. For semantically partitioned tables, a local index has partitions that are equipartitioned with their base table; that is, the table and index share the same partitioning key and partitioning type.

For all partitioned tables with local indexes, each local index partition has one and only one corresponding data partition.
A global index – spans all data partitions in a table. SAP supports only unpartitioned global indexes. All unpartitioned indexes on unpartitioned tables are global.

You can mix partitioned and unpartitioned indexes with partitioned tables:

A partitioned table can have partitioned and unpartitioned indexes.
An unpartitioned table can have only unpartitioned, global indexes.

Partition IDs

A partition ID is a pseudorandom number similar to object ID. Partition IDs and object IDs are allocated from the same number space. An index or data partition is identified with a unique combination of index ID and partition ID.

Locks and Partitions

Partition locks increases data availability by creating finer locking granularity, which allows access to other partitions for concurrent DDL and DML statements.

See Performance and Tuning Series: Locking and Concurrency Control.

Partitioning Types
SAP ASE supports range partitioning, hash partitioning, list partitioning, and round-robin partitioning.
Partition Pruning
Partition pruning, or partition elimination, can save considerable time and resources during execution.
Composite Partitioning Keys
Semantically partitioned tables have one partition key per table or index. For range- or hash-partitioned tables, the partition key can be a composite key with as many as 31 key columns.
Indexes and Partitions
Indexes speed data retrieval by pointing to the location of a table column’s data on disk. You can create global indexes and local indexes, each of which can also be clustered or nonclustered.
Create and Manage Partitions
The sp_configure option enable semantic paritioning turns on semantic paritioning. Round-robin partitioning is always available, and is unaffected by the value of enable semantic partitioning.
Change Data Partitions
The alter table alters data partitions.
Configure Partitions
You can configure partitions to improve performance.
update, delete, and insert in Partitioned Tables
The syntax to update, insert, and delete data in partitioned tables is the same as for unpartitioned tables. You cannot specify a partition in update, insert, and delete statements.
Update Values in Partition-Key Columns
For semantically partitioned tables, updating the value in a partition-key column can move the data row from one partition to another.
Display Information About Partitions
Use sp_helpartition to view information about partitions.
Truncate a Partition
You can delete all the information in a partition without affecting information in other partitions.
Using Partitions to Load Table Data
You can use partitioning to expedite loading large amounts of table data, even if the table will eventually be used as an unpartitioned table.
Update Partition Statistics
The SAP ASE query processor uses statistics about the tables, indexes, partitions, and columns in a query to estimate query costs. The query processor chooses the access method that it determines to be the least expensive. But to do so, it must have accurate statistics.
Improved Concurrency for Partition-Level Online Operations
Certain partition-level operations can concurrently operate on different partitions of a table. DML can also concurrently operate on the table while the partition-level online operation is running.