What is update global indexes?

What is update global indexes?

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE ) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions.

How do you change index status from unusable to valid?

To make an index unusable:

1. Query the data dictionary to determine whether an existing index or index partition is usable or unusable.
2. Make an index or index partition unusable by specifying the UNUSABLE keyword.
3. Optionally, query the data dictionary to verify the status change.

Why does an index become unusable?

Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. Table partition maintenance – Alter commands (move, split or truncate partition) will shift ROWID’s, making the index invalid and unusable.

When to use the update global indexes clause?

However, the UPDATE GLOBAL INDEXES clause may be used with DROP PARTITION, TRUNCATE PARTITION, and EXCHANGE PARTITION operations to keep the global indexes on index-organized tables usable. For the remaining operations in the above list, global indexes on index-organized tables remain usable.

How to update global indexes in Oracle 9?

In Oracle 9, a workaround to the problem was introduced, the UPDATE GLOBAL INDEXES clause. This keeps the (global) index entries valid by correcting the index entries as the partition operation is performed.

Why are indexes in unusable state in Oracle?

In early releases of Oracle this could create problems in terms of availability for partitioned tables, because if you tried to use an index that had become unusable, you would get an error: ORA-01502: index or partition of such index is in unusable state, which is not the most pleasant thing to be sending back to your application users

Do you have to update indexes after a partition?

You are not required to update later and independently rebuild the indexes. The global indexes are more highly available, because they are not marked UNUSABLE. These indexes remain available even while the partition DDL is executing and can access unaffected partitions in the table.