New Features for Administrators - Question 093

An index-organized table (IOT) called SPARSE has had many records deleted.

Which command reorganizes the IOT so that the empty space is removed, while minimizing the impact for users?

A. EXPORT with TABLES and IMPORT with the INDEX and IGNORE options.

B. ALTER TABLE _ COALESCE ;

C. ALTER INDEX _ REBUILD ONLINE ;

D. CREATE TABLE _ AS SELECT _ ONLINE;

Answer: B

Explanation:

The ALTER TABLE table_name COALESCE statement will reorganize the IOT. The rebuild operation creates a new tree to defragment the index, as opposed to the coalesce operation that coalesces the leaf blocks within the same branch of the tree.

Incorrect Answers

A: You cannot do this using EXPORT and IMPORT with specified options. There is no INDEX option in EXPORT utility. Only INDEXES option exists.

C: This command can be used to rebuild the secondary index of IOT only.

D: This command is incorrect: you cannot use the ONLINE option that way.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 76-78 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53 Chapter 2: Oracle9i Architecture Changes