Maintaining Indexes

Monitoring Index Space

Indexes on tables that have undergone multiple updates and deletes may use unnecessary space. As rows are added to indexes, the indexes increase in number of pages and number of levels. As those rows are updated and (especially) deleted, the pages become sparsely populated, reducing the amount of used space per index page. To compress the indexes, delete them and recreate them. Remember, to make the most of clustering indexes, the table must be unloaded and sorted on the index, and then reloaded after the index is deleted and created again. To determine the amount of space used by an index, check the INDEX PAGES field on the Static Size screen in SQLMON.

Tables that make heavy use of indexes should be located on a different disk drive than their indexes. In order to be able to do this you must have the table and index data stored in separate DBEFiles. This is most easily accomplished using DBEFile types of INDEX and TABLE to restrict the contents of the DBEFiles.

Tables that are used together frequently should be located on separate drives. Again, the tables must be stored in separate DBEFiles for this to be possible. The easiest way to ensure that tables are stored in separate DBEFiles is to create them in separate DBEFileSets. Then you can use MOVEFILE to move all DBEFiles in one DBEFileSet to a different device.

Monitoring the Cluster Count

The cluster count of an index indicates how many times ALLBASE/SQL has to access a different data page to retrieve the next row during an index scan. The greater the cluster count, the greater the potential for I/O when a query that uses that index is processed.

The CCOUNT column in the SYSTEM.INDEX view shows the cluster count of each index at the time of the last UPDATE STATISTICS statement. For a given data page, when the value for each key is different from all previous values on the page, the cluster count is incremented for the index. After a significant amount of table modification, the cluster count of an index may increase, which can hinder performance.

The CCOUNT is used by ALLBASE/SQL to determine whether or not to use an index to retrieve data. The higher the CCOUNT, the less likely ALLBASE/SQL will perform the query using an index scan. It will instead perform the query using a serial scan or a different index.

Although indexes are dynamically updated, information in the system views is not. It is important to execute the UPDATE STATISTICS statement after significant modifications have been made to any table or index in order to update the information, such as CCOUNT, used by ALLBASE/SQL.

To monitor the cluster count with SQLMON, invoke the Static Cluster screen and compare the value of the CCOUNT field with the values of the TABLE PAGES and TOT TUPLES fields. Performance is best when CCOUNT is equal to TABLE PAGES. As CCOUNT approaches TOT TUPLES, performance degrades.

The cluster count of an index is one measure of its efficiency. All indexes, clustering and nonclustering, have a cluster count. For a discussion of the differences between clustering and nonclustering indexes, refer to the "Logical Design" chapter.

Dropping and Recreating an Index

Indexes are dynamically updated when INSERT, UPDATE, or DELETE operations are performed on a table. To minimize overhead during large inserts, updates, or deletes, you can delete indexes with large keys and recreate them after the changes are made. This depends, of course, on table size versus insert, update, or delete size. For example, if you have one million rows in the table and you are updating one hundred of them, deleting the indexes will cause more overhead than leaving them intact.

As rows are inserted into a table, the indexes expand in number of pages and in number of levels. As rows are updated or deleted, the number of index pages may decrease, but the number of levels remains the same. During deletes, the number of occupied index pages will decrease as pages are emptied, but those pages not completely emptied will be sparsely populated causing an unnecessarily high number of index levels. For performance reasons and storage efficiency, you should drop and recreate indexes after multiple updates and deletes to a table.

Temporary indexes can be used by applications to improve performance. Applications that run on a periodic basis (once a month, for example) can create indexes to use while they are running and drop the indexes before terminating. (The DDL Enabled flag in the DBECon file must be set to YES in order to create and drop temporary indexes.) This way application data access is optimized at run time and overhead is minimized for updates when the application is not being run.

In all cases, you need to weigh the size of the table against the amount of work that is required to drop and recreate the index. In general, if your tables are extremely large, you should only drop and create indexes if you are experiencing poor performance or if you need to load a large amount of data. For smaller tables, you may want to recreate indexes periodically to ensure optimal performance.

Reloading a Table to Improve Index Performance

Another approach to index maintenance is to unload and reload tables in key order, which can improve performance. Use the following steps:

Altering the Index Key

You can change an index key at any time without disturbing the table data by dropping the old index and creating a new index with a different key. However, when you change a clustering index key, the rows already in the table are not rearranged for clustering. New rows inserted into the table after the new index is created are clustered according to the new key. To change the key in a clustering index and rearrange the rows currently in the table, you must follow the steps illustrated above. In step one, be sure to use an ORDER BY clause that reflects the new clustering key.