What is local and global index?
Local index is where the index is equipartitioned with its table, the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its table.
So each index partition is associated with exactly one partition of the underlying table, that is, all keys in an index partition refer only to rows stored in a single corresponding table partition. Local index can be subpartitioned.
Example for local index definition:
CREATE BITMAP INDEX [user_name].TEST_IDX ON [user_name].TESTTABL (CUST_ID) LOCAL;
Global index is an index which is not local. It can be partitioned or non partitioned. If it is partitioned, then it is normally not equipartitioned with the table, a single index partition can point to any or all table partitions. (It is possible to create a global index which is equipartitioned with its table, but it does not make sense doing that as Oracle will not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations.) The highest partition of a global index must have a partition bound that includes all values that are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.
Global index cannot be subpartitioned. Note, you can create partitioned index on non partitioned table as well.
What is prefixed and non prefixed index?
The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non prefixed.
Can I create nonprefixed global partitioned index?
No, Oracle does not support global nonprefixed partitioned indexes. Global partitioned index must be prefixed, otherwise ORA-14038 occurs when trying to create such.
Can I create UNIQUE partitioned index?
A local index can be created UNIQUE if the index partitioning columns form a subset of the index columns (e.g. local prefixed index always satisfies the latter condition). This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.
Global partitioned index can be created UNIQUE index since the partitioning columns always form a subset of the index columns (global partitioned index must be prefixed).
Can I create partitioned bitmap index?
Yes, as a local index. Bitmap index cannot be global partitioned index, it can only be local or non partitioned (hence bitmap index on nonpartitioned table cannot be partitioned).
What types of partitioning can I choose for a global index?
Global partitioned index can be partitioned only by range or by hash. In a range partitioned global index, each index partition contains values defined by a partition bound. In a hash partitioned global index, each partition contains values determined by the Oracle Database hash function.
Can I subpartition global index?
No you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have subpartitioned table with local index.
Why indexes on partitioned table become UNUSABLE?
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the global indexes and affected local index partitions. At partition maintenance of the table, all partitions of a global index are affected. You must then rebuild the entire index or each of the affected index partitions.
Whether an operation marks the affected index partition unusable depends on the partition type (e.g. add partition does not mark global and affected local indexes unusable for range partitioned table, but it may do for hash partitioned table).
What happens with my queries against partition table having unusable indexes?
Unusable indexes are ignored by the optimizer (by default), this is due to instance parameter.
SKIP_UNUSABLE_INDEXES = [TRUE | FALSE ] (default value: TRUE)
This setting disables error reporting of indexes and index partitions marked UNUSABLE, because the optimizer choses access path alternative to the unusable index or index partition. If you do not want the database to choose an alternative execution plan to avoid the unusable elements, you should set this parameter to FALSE.
Partitioned indexes can be used by the optimizer even if some partitions are unusable. Prior to 11.2 this can be done only when static partition pruning occurs (when partition elimination occurs at parse time as opposed to at runtime) and only access of usable index partitions mandatory. With 11.2, intelligent rewrite of queries using UNION ALL in the presence of partially unusable indexes was introduced, which provides transparent internal rewrite, usable index partitions will be used, full partition access for unusable index partitions.
How can I prevent indexes become UNUSABLE during maintenance operation of table partitions?
If you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index (both global and affected local index partition) at the time it executes the maintenance operation DDL statement.
In order to update global indexes only, use UPDATE GLOBAL INDEXES clause. The following operations support the UPDATE INDEXES clause:
ADD PARTITION | SUBPARTITION COALESCE PARTITION | SUBPARTITION DROP PARTITION | SUBPARTITION EXCHANGE PARTITION | SUBPARTITION MERGE PARTITION | SUBPARTITION MOVE PARTITION | SUBPARTITION SPLIT PARTITION | SUBPARTITION TRUNCATE PARTITION | SUBPARTITION
The following implications are worth noting when you specify UPDATE INDEXES:
- The partition DDL statement takes longer to execute, because indexes that otherwise would be marked UNUSABLE are now updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less than 5% of the size of the table.
- The DROP, TRUNCATE and EXCHANGE operations may no longer be fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all the indexes.
- When you update a table with a global index, the index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode. Furthermore rebuilding the entire index manually may be able to create a more compact, more efficient index.
- If an index or index partition was unusable before the partition maintenance operation (pmop), then it stays unusable after pmop as well even if the update indexes clause was specified.
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.