Chapter 10: Partitioning Tables and Indexes

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.

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

Figure 10-6 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

Figure 10-6: Local clustered index – unique

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

The example in Figure 10-7 is indexed on the pub_name column. It cannot enforce uniqueness. See “Guaranteeing a unique index”.

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

Figure 10-7: Local clustered index– not unique

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.

View this book as PDF