Chapter 10: Partitioning Tables and Indexes

Global clustered index on round-robin partitioned table

Adaptive Server supports unpartitioned, clustered global indexes for round-robin partitioned tables only. This type of index is consistent with tables partitioned in Adaptive Server 12.5.x.

If a round-robin table has more than 255 data partitions, you cannot create a global index on that table. You must create a local index.

An unpartitioned index allows a full table scan on all partitions. Because the leaf index page is also the data page on APL tables, this index is most useful when all data partitions reside on the same segment. You must create the index on the data-partitioning key.

For this example, see Figure 10-3, we create three round-robin partitions on the publishers table from pubs2. Make sure you drop any indexes before creating the partitions.

alter table publishers partition 3

To create a clustered index on the round-robin partitioned publishers table, enter:

create clustered index publish1_idx
	on publishers(pub_id)

Figure 10-3: Global clustered index on a round-robin partitioned table

This figure shows a global clustered index on pub_id column from 0 to 300. Below there are leaf pages showing table partition a, with pub_id 0 to 100, table partition b, with pub_id 101 to 200, and table partition c, with pub_id 201 to 300.

Global nonclustered index on partitioned table

View this book as PDF