The Geek Diary

What are Local and Global Indexes in Oracle Database

by

Background information

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute.

In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions. A global index can be range or hash partitioned, though it can be defined on any type of partitioned table. A global index is created by specifying the GLOBAL attribute.

The Partial Global index feature is not supported for unique indexes, or for indexes used to enforce unique constraints. Also, you cannot specify the indexing_clause for index-organized tables. When an index is created as PARTIAL on a table:

  • Local indexes: An index partition is created usable if indexing is turned on for the table partition, and unusable otherwise. (You can override this behavior by specifying the USABLE or UNUSABLE properties at the index or index partition level in the create index statement.)
  • Global indexes: Include only those partitions for which indexing is turned on, and exclude the others.

Global Index

A global index is a one-to-many relationship, allowing one index partition to map to many table partitions. The docs say that a “global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or non-partitioned, table”.

Syntax:

CREATE INDEX item_idx
    on all_fact (item_nbr)
    GLOBAL
    (PARTITION city_idx1 VALUES LESS THAN (100)),
    (PARTITION city_idx1 VALUES LESS THAN (200)),
    (PARTITION city_idx1 VALUES LESS THAN (300)),
    (PARTITION city_idx1 VALUES LESS THAN (400)),
    (PARTITION city_idx1 VALUES LESS THAN (500));

Local Index

A local index is a one-to-one mapping between an index partition and a table partition. In general, local indexes allow for a cleaner “divide and conquer” approach for generating fast SQL execution plans with partition pruning.

Syntax:

CREATE INDEX year_idx
    On all_fact (order_date)
    LOCAL
    (PARTITION name_idx1),
    (PARTITION name_idx2),
    (PARTITION name_idx3);

How to update both global and local indexes when moving table partition?

You can use ‘UPDATE INDEXES’ clause to update both global and local indexes during partition move.

For example:

SQL> ALTER TABLE T1 MOVE PARTITION PART_200903 TABLESPACE users
UPDATE INDEXES;

or

SQL> ALTER TABLE T1 MOVE PARTITION PART_200903 TABLESPACE users
UPDATE INDEXES (IDX_T1_01(PARTITION PART_200903));

What are the performance implications of local indexes?

– Availability is increased because actions that make data invalid or unavailable in a partition affect this partition only.
– Partition maintenance is simplified. When moving a table partition, or when data ages out of a partition, only the associated local index partition must be rebuilt or maintained.
– Partition elimination/pruning during SQLs against the partitioned table with predicate on the partition key (prefixed more often allows for partition elimination than non prefixed).
– Non prefixed local index is useful if it is important to have quick access according to a column which is not the partition key (e.g. look up for value account_number column, hence the account_number is placed as a leading column of the index), while it is also important to have the index equipartitioned with the table e.g. to support the time interval for rolling out old data and rolling in new data (e.g. partition key is time_id column, rolling out/in data is done by partition maintenance commands). This scenario often happens in historical databases.
– Local prefixed index could be beneficial to “fast split” of partition. Oracle executes internal SQLs to decide whether fast split is possible or not. The execution plan of certain internal SQLs of such, e.g. to determine whether any of the resulting partitions will be empty, is done by queries like the following, which are to determine whether there are rows in the partition to be split relative to the AT value:

(i)  select /*+ FIRST_ROWS(1) PARALLEL("MY_PART_TABLE", 1) */ 1 from "MY_PART_TABLE" [SUB]PARTITION ("MY_PART_TABLE_1_MAX")  where (((("A"< AT_VALUE)))) and rownum < 2
(ii) select /*+ FIRST_ROWS(1) PARALLEL("MY_PART_TABLE", 1) */ 1 from "MY_PART_TABLE" [SUB]PARTITION ("MY_PART_TABLE_1_MAX")  where (((("A" >= AT_VALUE OR "A" IS NULL)))) and rownum < 2

The above SQLs could take time, particularly when the table is large and global index is chosen to range scan according to partition key value. The existence of a prefixed local index could make it faster as it only needs to scan the index partition that belongs to the partition to be split, as shown below:

Row Source Operation
-----------------------------------------------------------
  COUNT STOPKEY
   PARTITION RANGE SINGLE PARTITION: 3 3
    INDEX ... SCAN  PARTITION: 3 3

- Local indexes simplify the task of tablespace incomplete recovery. To recover a partition or subpartition of a table to a point in time, you must also recover the corresponding index entries to the same point in time. The only way to accomplish this is with a local index. Then you can recover the corresponding table and index partitions or subpartitions.
- Unusable indexes do not consume space starting from 11.2.

What are the performance implications of global indexes?

- Global index can be useful where rapid access, data integrity, and availability are important. In an OLTP system, a table may be partitioned by one key, for example, the employees.department_id column, but an application may need to access the data with many different keys, for example, by employee_id or job_id. Global indexes can be useful in this scenario as global indexes are prefixed and can provide better performance than local nonprefixed indexes because they minimize the number of index partition probes (cf. local prefixed more often allows for partition elimination than non prefixed mentioned in the previous section).
- To create UNIQUE index if the table partitioning columns do not form a subset of the index columns, hence local unique index cannot be created.
- Global indexes are harder to manage than local indexes. At partition maintenance of the table, all partitions of a global index are affected.
- Partition elimination/pruning during SQLs against the partitioned table: prefixed - always allows for partition elimination.
- The hash index partitioning can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. In some OLTP applications, index insertions happen only at the right edge of the index. This situation could occur when the index is defined on monotonically increasing columns (e.g. column value is populated by a sequence). In such situations, the right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index maintenance activity, which results in performance degradation.

With hash partitioned global indexes index entries are hashed to different partitions based on partitioning key and the number of partitions. This spreads out contention over number of defined partitions, resulting in increased throughput.

With hash partitioning, an index entry is mapped to a particular index partition based on the hash value generated by a hash function in the Oracle Database. In order to be the hash partitioning effective the number of hash partitions should be the power of 2 and the values tuples of the partition key column(s) should be sufficiently large compared to the number of hash partitions so that balance of hash partitioned can be achieved. Having 5 distinct values of the hash partition key that needs to be mapped to 8 hash partition cannot eliminate skew if it exists as the hash function is deterministic, that is, to the same partition key value, the same hash partition is mapped, on the other hand different partition key values could map to the same hash partition.

The syntax to create hash-partitioned global index is very similar to hash-partitioned table. Queries involving equality and IN predicates on index partitioning key can efficiently use global hash partitioned index to answer queries quickly by eliminating index partitions.

- Unusable indexes do not consume space starting from 11.2.

How to Determine Whether an Index is Global or Local

To determine whether an index is global or local, query the column, LOCALITY, in DBA_PART_INDEXES.

The only corner case which this doesn't cover is when the table is partitioned, but has a global index which is ** not ** partitioned, since such an index would not be tracked by DBA_PART_INDEXES. However, one could deduce that case by comparing the column, PARTITIONED, of DBA_INDEXES with the same column in DBA_TABLES. Finding a value of 'NO' in the former, but 'YES' in the latter, would signal a non-partitioned, global index.

You May Also Like