Managing Indexes

After you create indexes, continue to evaluate and tune their effectiveness in the following ways:

• Examine query and transaction plans to find out if any indexes are rarely or never used. Drop unused indexes.
• Verify the efficiency with which indexes use their allocated space and ensure that the index is valid.
• Make sure that index builds are performed efficiently.

Maintaining Index Space Efficiency

Frequent updates tend to expand the index structure, so that it contains many partly full leaf pages. When an application updates a table in such a way that indexes must also be updated, the database server btree cleaner removes deleted index entries and balances the index nodes. However, depending on the order in which your application adds and deletes keys from the index, the structure of an index might become inefficient.

Use the onutil CHECK ALLOCATION INFO command with the TABLESPACE option to find out how much free space is in each index page. If the table has relatively little update activity and index pages contain a large amount of free space, drop and re-create the index with a larger value for FILLFACTOR to compress index entries on each page and make the unused disk space available for other uses.

You can also use the onutil CHECK INDEX command to verify the integrity and consistency of B-tree indexes.

For information about how the database server maintains an index tree, refer to the IBM Informix: Extended Parallel Server Administrator's Reference:

Increasing Concurrency During Index Checks

To verify that indexes are correctly constructed and contain valid entries, use the onutil CHECK INDEX command. This command checks the order of key values and the consistency of horizontal and vertical node links for B-tree indexes and bitmaps that are associated with the specified table and provides information about how efficiently the index is using its allocated space.

By default, the onutil CHECK INDEX command opens all index fragments with an intent shared lock. However, if you add the LOCK keyword to the onutil CHECK INDEX command, it uses intent shared locks instead. An intent shared lock allows other users to insert or modify data in the indexed table while the index is being checked, but it does not permit dropping or altering the table during the index check.

Because users can insert and modify data while the index is being checked with an intent shared lock, the index might contain some inconsistencies even after it is checked. To make sure that an index is completely consistent, use the LOCK keyword, which causes onutil to acquire a share lock on the index fragments before performing the check. A share lock prevents other users from modifying the table during the check.

For example, to use intent shared locks while you check the index custidx in the database YR97, enter the following command:

onutil
1> CHECK INDEX yr97#custidx;

To prevent other users from modifying the index while it is being checked, and to give onutil the best opportunity to find inconsistencies, enter the following command:

onutil
1> CHECK INDEX yr97#custidx LOCK;

For detailed information about the onutil CHECK INDEX command, refer to the IBM Informix: Extended Parallel Server Administrator's Reference.

Dropping Indexes

When an update transaction commits, the database server btree cleaner removes deleted index entries and, if necessary, rebalances the index nodes. However, depending on the order in which the application adds and deletes table rows, the structure of an index on the table can become inefficient.

To determine the amount of free space in each index page, use either ISA or the onutil INFO command with the IN TABLESPACE option, as described in the IBM Informix: Administrator's Reference. If a table has relatively low update activity and index pages contain a large amount of free space, you might drop and re-create indexes on the table with a higher value for FILLFACTOR to make the unused disk space available for other uses.

Improving Performance for Index Builds

When the database server creates an index, it performs both scans and sorts in parallel. The number of parallel processes depends primarily on the number of fragments in the indexed table and the resulting index.

You can often improve the performance of an index build by taking the following steps:

1. Estimate the amount of virtual shared memory that the database server might need for sorting.
2. Ensure that the memory specified by DS_TOTAL_MEMORY is sufficient. If it is not, ensure that enough temporary space is available for overflow.
3. Execute the SET ENVIRONMENT IMPLICIT_PDQ ON statement to allow the database server to allocate enough memory for the index build. For information about tuning the automatic optimizer estimates, see Enabling Optimizer-Determined Memory Allocation.
4. If your estimates indicate that the available shared memory is insufficient, execute the SET ENVIRONMENT TMPSPACE_LIMIT OFF statement to ensure that the index build can use as much temporary space as it needs. For more information, see Limiting Temporary Space for Query-Operator Overflow.

For more information, refer to Appendix A. Estimating Table and Index Size.