While observing the index statistics, you find that an index is highly fragmented, thereby resulting in
poor database performance. Which option would you use to reduce fragmentation without affecting
the users who are currently using the index?
A.
Validate the index structure using the ANALYZE …. INDEX command
B.
Rebuild the index using the ALTER INDEX ….. REBUILD ONLINE command
C.
Change the block space utilization parameters using the ALTER INDEX command
D.
Deallocate the unused space in the index using the ALTER INDEX … DEALLOCATE UNUSED
command
With the ALTER INDEX statement, you can:
Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.
The following statement rebuilds the existing index emp_name:
ALTER INDEX emp_name REBUILD;
The REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used in conjunction with the DEALLOCATE UNUSED clause.
You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the emp_name index online:
ALTER INDEX emp_name REBUILD ONLINE;
Rebuild unusable indexes will re-create the index at a new location, and then deletes unusable indexes.
ALTER INDEX HR.emp_empid_pk REBUILD ONLINE;
Use REBUILD ONLINE clause, users can continue to update the index table during the re-building process.