5.6 How do I… Enforce uniqueness via an index?Problem
I have found an index that is unbalanced, after following the steps in How-To 5.4. For performance reasons, I want these indexes to be rebuilt. How do I rebuild unbalanced indexes?
Technique
Use the ALTER INDEX INDEX_NAME REBUILD command (UNRECOVER-ABLE is an option). With indexes, you can always rebuild them because they are driven by table data. Thus, you should always use UNRECOVERABLE, especially for large indexes. The only loss with this option is that the index is unrecoverable in the event of a crash, but it can be rebuilt easily.
Steps
1. Connect to SQL*Plus as the WAITE user account. Assume that the EMPLOYEE05 table has been in use for a while, and hundreds of thousands of records have been inserted, deleted, and updated. This might cause the index SSN_INDEX to become unbalanced. For this How-To, we will pretend that the index needs to be rebuilt.
The syntax of the ALTER INDEX … REBUILD command is shown in Figure 5.11.
2. Run the CHP5_10.SQL script to rebuild an index. The script will prompt you for an owner and an index name. For this How-To, enter WAITE for the OWNER, and SSN_INDEX for the INDEX_NAME. This interaction is shown in Figure 5.12.
How It Works
Any index may be rebuilt by running the CHP5_10.SQL script. The user is prompted for the owner and index name, which is then rebuilt.
The UNRECOVERABLE option is used to improve performance. This option bypassed redo usage and reduces sorting processing while rebuilding the index. The larger the index is, the more effective the UNRECOVERABLE option becomes. The only negative aspect is that the index may become corrupt if the database were to crash during the rebuild. This really does not matter, as you can simply re-create or rebuild the index when the database is brought back up. As long as the table that the index is based upon remains intact, an index can always be re-created.
Comments
The REBUILD option of the ALTER INDEX command saves you from remembering storage parameters, dropping the index, and writing a CREATE INDEX statement.