Reorganizing a Table

[ Team LiB ]

Reorganizing a Table You can reorganize a table immediately or schedule it for a specific date and time. When you reorganize a table, the table data is rearranged into a physical sequence, usually according to a specified index. As a result, SQL statements on that data can be processed more efficiently. Also, the reorganization process removes unused, empty space from the table, and the reorganized table is stored more compactly. Use the following instructions to reorganize your table immediately: Before you reorganize tables, collect the statistics of the data as described in the preceding section. In the Control Center, click the Tables icon to see a list of tables available. In the contents pane, right-click the table you want to reorganize and select Reorganize from the pop-up menu. The Reorganize Table dialog box opens (see Figure 19.19). Accept the defaults for this example. Figure 19.19. The Reorganize Table dialog box. Tip It's generally recommended that you specify a temporary SMS table space. If the table you're reorganizing resides in a DMS table space, and you think the temporary space will fit in the same table space, don't specify a table space in this combo box. The reorganization runs faster in this case. Specifying a DMS table space as the temporary table space isn't generally recommended. If you specify a DMS table space in this field, you can reorganize only one table in that table space at a time. Click OK to reorganize the table immediately. Alternatively, click Schedule to open the Schedule window to schedule the reorganization for a specific time or date. Tip You may want to schedule this activity because reorganizing data can be time-consuming and users won't be able to access the table being organized. When the tables are reorganized, collect the statistics again as explained earlier in the section "Collecting Statistics." Doing so provides the most up-to-date statistics for the data so that you can get the fastest access to it based on the new organization of the data and indexes. Tip You may want to issue the REORGCHK command, which calculates the statistics on the database and provides a recommendation if the tables or indexes, or both, need to be reorganized. You should rebind applications that use static SQL after collecting statistics because the SQL optimizer may choose a different access plan given the new statistics. In particular, you should rebind those programs that reference tables for which the new statistics are available. (See Day 11, "Accessing the Data.")

[ Team LiB ]