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.

You can create local indexes on range-, hash-, list-, and round-robin–partitioned tables. Local indexes allow multiple threads to scan each data partition in parallel, which can greatly improve performance.

Local Clustered Indexes

When a table is partitioned, rows are assigned to a partition based on value, but the data is not sorted. When a local index is created, each partition is sorted separately.

This figure shows an example of partitioned clustered indexes on a partitioned table. The index is created on the pub_id column, and the table is indexed on pub_id. This example can enforce uniqueness on the pub_id column.

To create this table on the range-partitioned publishers table, enter:

create clustered index publish6_idx 
	on publishers(pub_id)
	local index p1, p2, p3

This figure shows a local clustered index. Both the index and the base table are partitioned on the pub_id column.

To create this example on the range-partitioned publishers table, enter:

create clustered index publish7_idx 
	on publishers(pub_name)
	local index p1, p2, p3

This figure shows a local clustered index; it is range partitioned on the name column. The base table is range partitioned on the pub_id column.

The information in each data partition conforms to the boundaries established when the partitions were created, which means you can enforce unique index keys across the entire table.

Local Nonclustered Indexes

You can define local nonclustered indexes on any set of indexable columns.

Using the publishers table partitioned by range on the pub_id column as in , create a partitioned, nonclustered index on the pub_id and city columns:

create nonclustered index publish8_idx (A)
	on publishers(pub_id, city)
	local index p1, p2, p3

You can also create a partitioned, nonclustered index on the city column:

create nonclustered index publish9_idx (B)
	on publishers(city)
	local index p1, p2, p3

This example shows both examples of nonclustered local indexes. The graphic description of each is identical. However, you can enforce uniqueness on example A; you cannot enforce uniqueness on example B.