Index Management and Performance Considerations

Index Management and Performance Considerations
Why to add an index?
What is the cost of adding an index?
Indexes design condifderation
Indexes maintenance consideration
What does the IDXANALYS qualifier provides?

The Progress database uses indexes to locate a specific row or a group of rows in a table. When a query is executed, one or more indexes will be selected to be used to retrieve the requested data. Understanding index selection is important in the design of a database system. Improper use of indexes can lead to performance degradation for the process using the improper index adding a significant bottleneck which then affects the performance of the entire application system. 

Indexes are used for the following purposes:

• Fast access and retrieval of a specific row or set of rows.
• To retrieve rows in specific order.
• To enforce uniqueness of column values.
• To allow fast location of rows that contain a specific word or phrase.

Cost of Adding an Index:

Although indexes usually provide performance gains in the retrieval of a specific row or set of rows, there is a cost in adding indexes to a table:

• Slower create and delete operations on rows. Index entries need to be added or deleted when rows are added or deleted. The more indexes there are, the more index entries must be added or deleted.
• Slower updates of index columns. Index entries must be updated whenever a column that is a component of an index is updated with a new value.
• Additional storage space. Index entries occupy additional disk space.
• Additional administration and maintenance. An increase in number of indexes will increase the time to rebuild indexes. In addition, the increase in size of the database will result in more time to manage the functions such as backup and restore. Indexing everything, is really not a good design consideration.

Design Considerations:

As there are advantages and costs involved in adding indexes, therefore it is very important to understand the need for indexes and avoid unnecessary or redundant indexes. It is also a good idea to review the components of each index during design time along with the type of queries that will be performed on the table. This can help in reducing the number of indexes needed by organizing them to take advantage of multiple index selection. The following should be considered when evaluating the need for an index:

• How many rows are in the table?
• How often the rows will be accessed using this index?
• How many rows will be accessed using this index?
• Are there existing indexes which could be used instead?
• Is the row access required during on-line transaction processing or is it required for nightly batch runs?
• If it required for nightly batch runs, is the index really required?

Maintenance Considerations:

Use PROUTIL IDXANALYS qualifier to report on index blocks and utilization.
 
The IDXANALYS qualifier provides:

• A summary of indexes for the current database and the percentage of total index space used by each index.
• When IDXANALYS is run online, the information may not be precise.
• The index number
• The number of fields and levels in each index. The Levels field shows the number of reads Progress performs in each index per entry. 
• The size of each index, in Blocks and Bytes.
• A factor value that indicates whether to rebuild each index. The Factor field is based on the utilization and size of the index; it is an indicator of when indexes need attention. Consider the context of how the index is used. For example, if an index is highly active, with continuous insertions and deletions, its utilization rate varies greatly, and a rebuild is inadvisable. However, a static index with a high factor value benefits from a rebuild.
• The percent utilization within the index (that is, the degree of disk space efficiency). The most important field in the IXANALYS display is the % Util field. This field shows the degree of consolidation of each index. If an index is several hundred blocks, and the application most frequently retrieves this data, an index utilization of 85 percent or higher is optimal. There are two ways to increase an index's utilization rate:

1. Compress the index with the database online or offline with the PROUTIL IDXCOMPACT utility.
2. Rebuild and compress the index offline with the PROUTIL IDXBUILD utility.

Example:

Table                      Index  Fields Levels         Blocks    Size  % Util  Factor
PUB.tablename
  indexname                   14       4      4        1125499    2.3G    52.8     1.9