Rebuilding Multiple Indexes

Sometimes DBAs may need to rebuild indexes. For example, they might simply want to relocate an index to a different tablespace or rebuild it with different storage parameters. Other times, DBAs need to locate and then rebuild those indexes that have become unusable (that is, corrupted), whose B-trees have become unbalanced, or whose storage parameters were inappropriately sized for their growth needs. Whatever the reasons, TOAD provides a simple yet potent screen for making index rebuilds of any type a snap. Plus this screen can be run from the command line with e-mail notification capabilities. So TOAD can now fully automate your entire index rebuild candidate detection and code execution process via the Windows scheduler.

The Rebuild Multiple Indexes screen is accessible from the main menu at ToolsRebuild Multiple Indexes and is shown in Figure 6.24. When you first enter this screen, it will be blank. You must first load into this screen those indexes that you want to work with. You can accomplish this by selecting one of the four identical icons that look like a disk with an arrow pointing to a file folder, which represent Load My Indexes, Load Indexes by User, Load Indexes by Tablespace, and Load Indexes by Table. Figure 6.24 shows the Load Indexes by User window. After you've made an index filtering selection, the screen will be populated with all those indexes, their relevant sizing information, and check boxes for whether or not to include them for consideration as shown in Figure 6.25.

Figure 6.24. TOAD Rebuild Multiple Indexes ”filtering.

Figure 6.25. TOAD Rebuild Multiple Indexes ”selection.

After you've filtered and marked those indexes you're interested in, you can have TOAD review them by using the Examine Checked Indexes and Make Recommendations button (the flashlight shining on a disk icon). TOAD will display a dialog to warn you that the checked indexes will be examined, which will require a momentary lock on each index. After that process has completed, the screen will look like Figure 6.26. Note that a grid line has been added directly beneath each index on whether that index needs to be rebuilt or not. It also indicates why for those that are indicated as needing to be rebuilt. Do note that this can take a while if you select a schema or tablespace with lots of indexes. Be patient; TOAD displays its progress at the bottom of the window.

Figure 6.26. TOAD Rebuild Multiple Indexes ”results.

At this point, you could simply rebuild the indexes by using one of the toolbar icons related to execution: Rebuild Recommended Indexes or Rebuild Checked Indexes. Or you could copy the generated rebuild code to the clipboard for your own manual execution using either the Create Script to Rebuild Recommended Indexes or Create Script to Rebuild Checked Indexes icon. However, these index rebuild recommendations and the resulting code generated were made based on some default settings that most DBAs should review and adjust to their own specific needs. This is accomplished by using both the Thresholds and Performance Options tab shown in Figure 6.27 and the Storage Clause Adjustments tab shown in Figures 6.28 and 6.29.

Figure 6.27. TOAD Rebuild Multiple Indexes ”Thresholds and Performance.

Figure 6.28. TOAD Rebuild Multiple Indexes ”Extents.

Figure 6.29. TOAD Rebuild Multiple Indexes ” Tablespaces.

The top half of the Thresholds and Performance Options tab provides for advanced rebuild recommendation filtering based on thresholds. You can indicate to consider indexes only if they are bigger than a certain size or if they contain more than a specified number of extents. This would be useful for filtering out smaller minutia. You also can define when to mark indexes for rebuild based on their height, deleted rows percentage, and storage utilization. An index suffers height growth when there are B-tree splits , at which time it should be rebuilt. An index suffers from deleted row space consumption when lots of rows are deleted that are only marked as unused in the B-tree, at which time the index should be rebuilt. And finally, you don't want indexes with too much or too little space.

The bottom half of the Thresholds and Performance Options tab provides for advanced rebuild recommendation code generation based on performance options. You can indicate whether or not to rebuild the indexes in parallel and with nologging. Plus you can indicate whether to or not to set the indexes to nonparallel and logging after the rebuild is complete. You also can adjust your session's sort area size to accommodate the index creation sort space demands, plus reset that sort size after the process is complete.

The Storage Clause Adjustments tab offers numerous and key capabilities related to Extents and Tablespaces, shown in Figures 6.28 and 6.29 respectively. These two tabs provide a horde of capabilities, so review the following very carefully .

The Extents tab is a bit complicated as it enables you to define three distinct kinds of information. First, the top left portion of the screen offers check boxes for forcing and scaling certain storage parameter values. These options are fairly self-explanatory. The group box on the top right side of the screen permits you to define what is considered small, medium, large and huge based on either index or extent size. These settings are referenced on both the bottom portion of this screen and on the Tablespaces tab shown in Figure 6.29. The group box on the bottom of the screen permits you to force the index extent sizes based on the index's relative size. For example, medium-sized indexes (that is, those less than or equal to 5 megabytes in size) should use 512KB extents. This is a complicated tab. But if you spend the time to set all these options correctly, the rebuild scripts you can generate will be awesome .

The Tablespaces tab permits you to relocate indexes and index partitions from one tablespace to another. You can move them all or by their size. In Figure 6.29, the settings shown indicate to relocate small indexes to tablespace USER_DICT, medium indexes to tablespace QADB_DICT, large indexes to tablespace QADB_LOCAL, and huge indexes to tablespace DWDB_LOCAL. Moreover, all index partitions are to be rebuilt in tablespace DWDB_LOCAL. Remember, the meaning of small, medium, large, and huge was defined back on the Extents tab in Figure 6.28.

To receive the Rebuild Multiple Index candidate selection and code execution results via e-mail is a simple, two-step process. First you must select the Email Notification tab on this screen and select whether the message should be sent and in what format, as shown in Figure 6.30. The reason for HTML is because TOAD highlights and color -codes the output in a manner conducive to easy reading and focusing on key problems. Second, and only if sending e-mail after completion is enabled, you must visit the TOAD options category for Email Settings and define all the appropriate information as depicted in Figure 6.31.

Figure 6.30. TOAD Rebuild Multiple Indexes ” Email Notification.

Figure 6.31. TOAD e-mail options for rebuilding multiple indexes.

Remember that the TOAD Options screen can be opened either by using the Toolbox icon on the main toolbar or from the main menu at ViewOptions.

You're not quite done yet, though. The ideal scenario would be to have this report waiting for you each morning in your e-mail inbox already prepared and ready for inspection. That way you could start your day by quickly checking the index rebuild report statuses for your databases. To accomplish this, you must schedule this report to run on your Windows machine via the Microsoft Task Scheduler as shown in Figure 6.32. For Windows 2000, this is located at StartSettingsControl PanelScheduled TasksAdd Scheduled Task.

Figure 6.32. Windows scheduling of TOAD rebuild multiple indexes.

Note that you're simply scheduling Windows on your PC to run TOAD in command-line mode, where you provide the connection information and a rebuild multiple index (RMI) command text file. To produce that file is very simple. You merely need to open the screen, make all your selections, and then choose the Save All Settings to File toolbar icon. The generated file will contain everything necessary to start TOAD, connect to the database, and then run this screen. The generated command file will also contain comments on some commands you can add to tailor its behavior (for example, to close the screen and also shut down when the process is complete).