Local and global indexes on partitioned tables

Indexes, like tables, can be partitioned. Prior to Adaptive Server 15.0, all indexes were global. With Adaptive Server 15.0, you can create local as well as global indexes.

Adaptive Server supports local and global indexes.

  • A local index – spans data in exactly one data partition. For semantically partitioned tables, a local index has partitions that are equipartitioned with their base table; that is, the table and index share the same partitioning key and partitioning type. For all partitioned tables with local indexes, each local index partition has one and only one corresponding data partition. 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.
  • A global index – spans all data partitions in a table. Sybase supports only unpartitioned global indexes. All unpartitioned indexes on unpartitioned tables are global.
  • A partitioned table can have partitioned and unpartitioned indexes.
  • An unpartitioned table can have only unpartitioned, global indexes.

Local versus global indexes

  •  Local indexes can increase concurrency through multiple index access points, which reduces root page contention.
  • You can place local nonclustered index subtrees (index partitions) on separate segments to increase I/O parallelism.
  • You can run reorg rebuild on a perpartition basis, reorganizing the local index subtree while minimizing the impact on other operations.
  • Global nonclustered indexes are better for covered scans than local indexes, especially for queries that need to fetch rows across partitions.

Creating global indexes:
You can create global, clustered indexes only for round robin–partitioned tables. Adaptive Server supports global, nonclustered, unpartitioned indexes for all types of partitioned tables.
You can create clustered and nonclustered global indexes on partitioned tables using syntax supported in Adaptive Server version 12.5.x and earlier. When you create an index on a partitioned table, Adaptive Server automatically creates a global index, if you:

  • Create a nonclustered index on any partitioned table, and do not include the local index keywords. For example, on the hash‐partitioned table mysalesdetail.
    Example:
    create nonclustered index ord_idx on mysalesdetail (au_id)
  • Create a clustered index on a round robin–partitioned table, and do not include the local index keywords. For example, on currentpublishers table.
    Example:
    create clustered index pub_idx on currentpublishers

Creating local indexes:
Adaptive Server 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, Adaptive Server 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

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 below image
is indexed on the pub_name column; the table is partitioned on the pub_id column.

Global nonclustered index on a partitioned table (pub_name)

Global nonclustered index on a partitioned table (pub_name)

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)

The example in below image is indexed on the pub_id column; the table is also partitioned on the pub_id column.

Global nonclustered index on a partitioned table(column: pub_id)

Global nonclustered index on a partitioned table(column: pub_id)


To create a global nonclustered index on the pub_id column, enter:
create nonclustered index publish3_idx
on publishers(pub_id)

 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 “Global nonclustered index on partitioned table”, 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

Below image 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.

Local nonclustered indexes

Local nonclustered indexes

 

This entry was posted in RDBMS concepts and tagged , , , . Bookmark the permalink.

Leave a Reply