New Features for Administrators - Question 062

Examine the statement:

ALTER TABLE sales
EXCHANGE PARTITION q1_2000
WITH TABLE sales q1_2000
UPDATE GLOBAL INDEXES
PARALLEL (DEGREE 4);

What is the result of the statement?

A. All valid global indexes on the SALES table will retain their USABLE status after the statement completes.

B. Nonpartitioned indexes on the SALES table will be marked UNUSABLE, while valid partitioned global and local indexes will retain their USABLE status.

C. Valid indexes on the exchanged table SALES _Q1_2000 will remain in the USABLE state, while all indexes associated with the new partition Q12000, will be marked UNUSABLE, including any global indexes on SALES.

D. Global indexes on the SALES table will be maintained concurrently with the exchange operation if the statement executes with the desired degree of parallelism (four) otherwise it will execute serially and mark the global indexes UNUSABLE.

Answer: A

Explanation:

Oracle9i overcomes the problem of rebuilding the global index by giving you the option to update global indexes as Oracle performs the partition DDL. This feature is not applicable to local indexes, domain indexes, index-organized tables (IOTs), or to indexes that were UNUSABLE prior to start of the partition DML. You can invoke this capability by using the optional clause UPDATE GLOBAL INDEX of the ALTER TABLE command. You can use this clause with the ADD, COALESCE, DROP, EXCHANGE, MERGE, MOVE, SPLIT, and TRUNCATE partition DDL commands.

Incorrect Answers

B: Nonpartitioned indexes on the SALES table will not be marked UNUSABLE.

C: All valid global indexes on the SALES table will retain their USABLE status after the statement completes.

D: Global indexes on the SALES table will be maintained concurrently with the exchange operation disregarding if the statement executes with the desired degree of parallelism.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features