Consultor Eletrônico

Status: Verified

GOAL:

Index Management and Performance Considerations

GOAL:

Why to add an index?

GOAL:

What is the cost of adding an index?

GOAL:

Indexes design condifderation

GOAL:

Indexes maintenance consideration

GOAL:

What does the IDXANALYS qualifier provides?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10
OpenEdge Category: Language (4GL/ABL)

FIX:

The Progress RDBMS uses indexes to rapidly 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 very important in the design of a database system. Improper use of indexes may cause a significant bottleneck in the performance of a system. It could lead to performance degradation for the process using the improper index, and can also affect the performance of the entire 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. This is because index entries will be added or deleted when rows are added or deleted. The more indexes you have, the more index entries will have to be added or deleted.

-- Slower updates of index columns. This is because the index entries must be updated whenever a column that is a component of an index is updated with a new value.

-- Additional storage space. This is because the index entries will occupy additional disk space.

-- Additional administration and maintenance. This is because the 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.

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:

1) How many rows are in the table?

2) How often the rows will accessed using this index?

3) How many rows will be accessed using this index?

4) Are there existing indexes which could be used instead?

5) Is the row access required during on-line transaction processing or is it required for nightly batch runs?

6) If it required for nightly batch runs, is the index really required?

Maintenance Considerations:

-- Use PROUTIL's IDXANALYS qualifier to get information about index blocks and utilization.

-- To execute the IDXANALYS qualifier, enter the following command:

proutil db-name -C idxanalys

The IDXANALYS qualifier provides:

-- The number of fields and levels in each index.

-- The size of each index, in blocks and in bytes.

-- The percent utilization within the index (that is, the degree of disk space efficiency).

-- A factor value that indicates whether to rebuild each index.

-- A summary of indexes for the current database and the percentage of total index space used by each index.

NOTE: You can run PROUTIL with the IDXANALYS qualifier while the database is in use; however, Progress generates only approximate information.

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 your application most frequently retrieves data, an index utilization of 85 percent or higher is optimal. Ther.e are two ways to increase an index's utilization rate:

-- Compress the index with the database online or offline with the PROUTIL IDXCOMPACT utility.

-- Rebuild and compress the index offline with the PROUTIL IDXBUILD utility.

The Levels field shows the number of reads Progress performs in each index per entry. The Blocks and Bytes fields show you the size of each index. The Factor field is based on the utilization and size of the index; it is an indicator of when you should rebuild indexes. Table 12-3 provides a description of the different ranges of values for the Factor field. When you use the factor field to decide whether to rebuild an index, consider the context of how the particular 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..