Performance Tuning - Question 100

Which three statements about rebuilding indexes are true? (Choose three)

A. The ALTER INDEX REBUILD command is used to change the storage characteristics of an index.

B. Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses the fast full scan feature.

C. Oracle8i allows for the creation of an index or re-creation of an existing index while allowing concurrent operations on the base table.

D. When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to reduce the time it takes to rebuild.

Answer: A, B, C

Explanation:

You can use the ALTER INDEX REBUILD command to optimize the storage characteristics of an index. Using the ALTER INDEX command with the REBUILD option is an effective way to quickly rebuild an index because the existing index entries are used to create the new index. The ONLINE option of the ALTER INDEX REBUILD command should be used to minimize any locking issues that occur when an index is rebuilt while users continue to perform DML on the index’s underlying table.

Incorrect Answers

D: When building an index, the NOLOGGING and UNRECOVERABLE keywords cannot be used concurrently to reduce the time it takes to rebuild.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 151-152

Chapter 3: SQL Application Tuning and Design