Rebuild Index

Go Up to Available object actions by DBMS

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

• Rebuild Index (DB2 z/OS)

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

Rebuild Index (DB2 z/OS)

The Rebuild Index Wizard reconstructs indexes from the table that they reference.

To Rebuild One or More Indexes

1. On the Datasource Explorer, select the Indexes node or in the Datasource Navigator expand the node.
2. Right-click the target index select Rebuild Index from the context menu.

   The Rebuild Index Utility wizard opens.

3. Use the following table as a guide to understanding and modifying settings in the wizard:

Panel	Option	Description
1	Select Indexes	Lets you select an index to rebuild.
	Add	For more information, see Index Selector Dialog Box.
	Delete	Deletes the selected index(es).
2	Select tablespaces	Lets you select the target tablespace.
	Add	For more information, see Table Selector Dialog Box.
	Delete	Deletes the selected items.
3	Do you want the utility to be restartable?	Select to make the utility restartable.
	Do you want to reuse the db2 managed dataset?	Default is No.
	WorkDDN	Lets you select a work ddn template from the list or click Edit to specify the dataset information.
	Do you want to specify to sort device type?	Default is No.
	Do you want to specify the sort number?	Default is No.
	Do you want to specify the sort keys?	Default is No.
4	Do you want to specify the stats online?	Lets you specify the stats online.
	Do you want to output message to SYSPrint?	The default is no.
	Do you want to specify the correlation stats option?	Lets you specify correlation stats on the next panel of the wizard.
	Do you want to force aggregation or rollup processing to be done even though some parts do not contain data?	The default is Default.
5	KeyCard	Collects all of the distinct values in all of the 1 to n key column combinations for the specified indexes. n is the number of columns in the index.
	Frequent Item	Lets you add items to the grid.
	Add	For more information, see Add Frequent Item Dialog Box.
6	Update Statistics	Lets you specify update statistics.
	History Statistics	Lets you specify history statistics.

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.