Multiple choice

A partitioned table named ITEM is maintained. The following statement was issued:

ALTER TABLE ITEM DROP PARTITION p1 UPDATE GLOBAL INDEXES PARALLEL (DEGREE 3);

Which of the following statements represents the result of including the UPDATE GLOBAL INDEXES clause?

  1. After the P1 partition is dropped, all global B-tree and function-based indexes are automatically rebuilt.

  2. After the P1 partition is dropped, all global indexes that were marked UNUSABLE will be automatically rebuilt.

  3. All global indexes on the ITEM table remain intact throughout the DDL operation.

  4. Other DML statements can continue to use the any global B-tree indexes without interruption.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The UPDATE GLOBAL INDEXES clause ensures that dropping a partition does not invalidate global indexes. Instead of marking indexes UNUSABLE, Oracle asynchronously maintains them during the DDL operation. This allows concurrent DML operations to continue accessing global indexes without interruption, improving availability during partition maintenance.

Keep practicing — related questions