Global Vs Local Indexes

In this paragraph I discuss the various merits of global and local indexes, this is done partly for my own benefit, as it always helps to write things down, but I would also welcome your comments if you have an opinion on the subject or you think I’ve just got it plain wrong.

On a partitioned table, of whatever flavor (range, hash or list), you have a choice of local or global indexes.

Points to note: -

i) You can have a mix of both local and global indexes on the same partitioned table.

ii) Local indexes are best suited to data warehouse application, while global indexes work better with OLTP systems.

iii) Local indexes are ideal for any index that is prefixed with the same column(s) used to partition the table.

iv) Global indexes can be range partitioned using a different ‘scheme’ to the underlying table.

Local Indexes

Pros

i) The one major advantage of local indexes is that if a table partition is exchanged (using ‘alter table partitioned exchange/drop/add partition’) a local index will remain valid.

Cons

i) The disadvantage of a local index that is not prefixed with the column(s) that are used to partition the table is that in order to find a value(s) in the index, each separate partition has to be searched for the index entry/entries. When a relatively small number of rows are required from the database (typical of a OLTP system) this additional overhead can be a significant part of the query execution time. In a data warehouse system, were typically a large number of rows are returned from a query, this overhead is a smaller proportion of the overall time taken by the query.

Global Indexes

Pros

i) Global indexes can be range partitioned using a different partition scheme from the underlying table. i.e. on a table (containing two columns id and start_date) that is hash partitioned by id, the global index on start_date can be range partitioned. This allows for partition elimination of the index entries when performing queries with a predicate including the start_date column.

ii) They can be used to enforce uniqueness on columns not used to partition the table.

iii) All the index entries are held in one place (i.e. not spread out over ‘n’ partitions)

Cons

i) They go invalid when partitions are exchanged.