Effect of Split or Merged Partitions on Indexes

SAP ASE rebuilds all affected indexes when you perform a split, merge, or move partition on a table with indexes.

Note: Merging or splitting partitions removes statistics from systabstats and sysstatistics. SAP recommends that you run update statistics after merging or splitting partitions.
Splitting and Merging Partitions on Indexes

Command

Global Nonclustered Index

Local Index

Local Clustered Index

Local Nonclustered Index

split partition

Index is rebuilt

All affected index partitions are rebuilt

Rebuilds all index partitions

Rebuilt on default segment

merge partition

No effect if the source and destination segments are the same

All affected index partitions are rebuilt

Rebuilds all index partitions

Rebuilt on default segment

If the table you are splitting or merging includes indexes on separate segments, the segments on which the newly rebuilt indexes reside depend on the type of index.

Effect of Split Partition on Index Segments

Type of Index

After the Split or Merge Operation

Global nonclustered index

The index remains on the same segment as prior to the operation.

Local nonclustered index

  • New index partitions (corresponding to the split or merge destination data partitions) are placed on the segment specified at the index level.

  • The indexes are placed on the default segment if you do not specify an index segment.

  • All unaffected index partitions (corresponding to other data partitions that were not involved in the split or merge) remain on the same segment as prior to the split or merge operation

Local clustered index

  • New index partitions are placed on the same segment on which the corresponding destination data partition is placed.

  • The unaffected index partitions (corresponding to other data partitions not involved in the split or merge) remain on the same segment as prior to the split or merge operation.