HP 3000 Manuals HP 3000 Manuals
Maintaining Indexes
From time to time, an index becomes inefficient and needs to be modified
through maintenance operations. You can maintain indexes of different
kinds by using both simple and complex operations.
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:
* Unload the table using the INTERNAL option. Use a SELECT with an
ORDER BY on the index key in the UNLOAD command.
* Delete all rows in the table by omitting the WHERE clause in the
DELETE statement.
* Drop all indexes on the table.
* Load the table using the INTERNAL option.
* Recreate the indexes.
* Do an UPDATE STATISTICS on the table to update system catalog
information about the table and the index.
* COMMIT WORK.
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.