Rebuild Index

Go Up to Available object actions by DBMS

See the following topics for DBMS-specific instructions on rebuilding indexes:

• Rebuild Index (Oracle)
• Rebuild Index (SQL Server)

Rebuild Index (Oracle)

The Rebuild Indexes dialog lets you rebuild an index that has become fragmented. Rebuilding an index is a good alternative to coalescing an index because you can move the index to a different tablespace and change both tablespace and storage parameters while eliminating fragmentation. However, rebuilding an index has a higher cost than coalescing an index. These same qualities also make rebuilding an index a viable alternative to dropping an index then re-creating it.

As a rule of thumb, check indexes for rebuilds when their level (or tree depth) reaches four or greater, or many deleted leaf rows are found. The Rebuild Indexes dialog can also be used to easily move an index from one tablespace to another.

Important Notes

• If you are rebuilding a function-based index, the index is enabled when the rebuild is finished.
• You cannot rebuild a partitioned index. You must rebuild each partition or subpartition individually.

The table below describes the options and functionality on the Rebuild dialog.

Option	Description
New Tablespace	Defaults to the tablespace which currently includes the index. To change the tablespace containing the index, choose a new tablespace from the list.
Logging	Recoverable - The creation of the index logs in the redo log file. Non-Recoverable - The creation of the index is not logged in the redo log file.
Use Parallel Processes	Performs processes for the sequential execution of a SQL statement in parallel using multiple parallel processes. One process, known as the parallel execution coordinator, dispatches the execution of a statement to several parallel execution servers and coordinates the results from all of the server processes to send the results back to the user. NOTE: Only available for Oracle with the Parallel Server option. NOPARALLEL execution - Select this if you are concerned that the cost of synchronizing parallel processes will impede the throughput of data.
Order	Reverse - Instructs Oracle to store the bytes of the index block in reverse order and to exclude the ROWID when rebuilding the index. No Reverse - Instructs Oracle to store the bytes of the index block in normal order when rebuilding the index.

To Rebuild an Oracle Index

1. Initiate a Rebuild action against one or more indexes. For more information see Initiating an object operation.

   The Rebuild Indexes dialog opens.

2. To move the index to a new tablespace, click the New Tablespace list and then click the new tablespace.
3. In the Logging box, click:
   • The Recoverable option button to make the operation log in the redo file.
   • The Non-Recoverable option button if you do not want the operation logged in the redo file.
4. If you are using Parallel Server, select the Parallel Server check box and:
   • Type a value indicating the number of query server processes that should be used in the operation in the Degree box.
   • Type a value indicating how you want the parallel query partitioned between the Parallel Servers in the Instances box.
5. In the Order box:
   • Click the Reverse option button to rebuild the index to store the bytes of the index block in reverse order.
   • Click the No Reverse option button to rebuild the index to store the bytes of the index block in order.
6. Click Execute. For information on the scheduling option, see Scheduling.

Rebuild Index (SQL Server)

The Rebuild Indexes dialog lets you rebuild an entire index, primary key, or unique key or a single partition of those objects. Depending on your choice, a number of REBUILD WITH clause options are available.

To Rebuild an Index

1. Initiate a Rebuild Index action against one or more indexes, primary keys, or unique keys. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the Rebuild Index wizard:

Step	Settings and tasks
Action options	To rebuild a single partition, specify a Partition number and optionally, provide Sort in tempdb and MaxDOP property values used to create the REBUILD WITH clause. To rebuild the entire index, primary key, or unique key, DO NOT provide a Partition Number, and optionally, provide Pad Index, Sort in tempdb, Ignore Duplicate Key, Statistics no recompute, Online, Allow Row Locks, Allow Page Locks, MaxDOP, and Fill Factor property values used to create the REBUILD WITH clause. For more information on these properties, see Indexes Wizard (SQL Server).
Dependencies	Lets you review the objects potentially impacted by this action.
Preview	Preview the DDL generated for the operation. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.