Create Partitioned Indexes

Indexes can be created in serial or parallel mode, however, you can create global indexes on round-robin-partitioned tables only in parallel mode.

SAP ASE supports local clustered indexes and local nonclustered indexes on all types of partitioned tables. A local index inherits the partition types, partitioning columns, and partition bounds of the base table.

For range-, hash-, and list-partitioned tables, SAP ASE always creates local clustered indexes, whether or not you include the keywords local index in the create index statement.

This example creates a local, clustered index on the partitioned mysalesdetail table. In a clustered index, the physical order of index rows must be the same as that of the data rows; you can create only one clustered index per table.

create clustered index clust_idx 
	on mysalesdetail(ord_num) local index

This example creates a local, nonclustered index on the partitioned mysalesdetail table. The index is partitioned by title_id. You can create as many as 249 nonclustered indexes per table.

create nonclustered index nonclust_idx 
		on mysalesdetail(title_id)
	local index p1 on seg1, p2 on seg2, p3 on seg3

Create Clustered Indexes on Partitioned Tables

You can create a clustered index on a partitioned table if the select into/bulkcopy/pllsort database option is true, and if there are as many worker threads available as there are partitions.

To speed recovery, dump the database after creating the clustered index.

See a system administrator or the database owner before creating a clustered index on a partitioned table.