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:
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.