Chapter 10: Partitioning Tables and Indexes

Global nonclustered index on partitioned table

You can create global indexes that are nonclustered and unpartitioned for all partitioning table strategies.

The index and the data partitions can reside on the same or different segments. You can create the index on any indexable column in the table.

The example in Figure 10-4 is indexed on the pub_name column; the table is partitioned on the pub_id column.

For this example, we use alter table to repartition publishers with three range partitions on the pub_id column.

alter table publishers partition by range(pub_id)
(a values <= ("100"),

b values <= ("200"),

c values <= ("300"))

To create a global nonclustered index on the pub_name column, enter:

create nonclustered index publish2_idx
	on publishers(pub_name)

Figure 10-4: Global nonclustered index on a partitioned table

In Figure 10-5, the example is indexed on the pub_id column; the table is also partitioned on the pub_id column.

To create a global nonclustered index on the pub_id column, enter:

create nonclustered index publish3_idx
	on publishers(pub_id)

Figure 10-5: Global nonclustered index on a partitioned table