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.

In clustered indexes, the physical data is stored in the same order as the index, and the bottom level of the index contains the actual data pages. In nonclustered indexes, the physical data is not stored in the same order as the index, and the bottom level of the index contains pointers to the rows on the data pages.

Clustered indexes on semantically partitioned tables are always local indexes—whether or not you specify “local” index in the create index command. Clustered indexes on round-robin tables can be either global or local.

• Global Indexes
  Global indexes span data in one or more partitions, which are not equipartitioned with the base table. Because SAP ASE supports only unpartitioned global indexes, a global index spans all partitions.
• Local Indexes
  All local indexes are equipartitioned with the base table’s data partitions; that is, they inherit the partitioning type and partition key of the base table. Each local index spans just one data partition.
• Guarantee a Unique Index
  A unique index ensures that no two rows have the same index value, including NULL. The system checks for duplicate values when the index is created, if data already exists, and checks each time data is added or modified with an insert or update.