Tuesday, October 9, 2007

Types Of Partitioned Indexes

Indexes, like tables, may be partitioned. There are two possible methods to partition indexes. You may either:



Equi-partition the index with the table - Also known as a local index. For every table partition, there will be an index partition that indexes just that table partition. All of the entries in a given index partition point to a single table partition and all of the rows in a single table partition are represented in a single index partition.



Partition the index by range - Also known as a global index. Here the index is partitioned by range, and a single index partition may point to any (and all) table partitions.



In the locally partitioned index, the index entries in a given partition, point into exactly one table partition. The globally partitioned index diagram however, shows that the index entries in a global index may point into any or all of the table partitions. Also, note that the number of index partitions may in fact be different than the number of table partitions.



Since global indexes may be partitioned by range only, you must use local indexes if you wish to have a hash or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.

No comments: