Did this article resolve your question/issue?


Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025



Using USE-INDEX Explained

« Go Back


Article Number000001612
EnvironmentProduct: Progress / OpenEdge
Version: 9.x / 10.x, 11.x
OS: All Supported Operating Systems
Question/Problem Description
Using USE-INDEX Explained
How does Progress chooses an index
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number

The following section describes the index selection process using the Sports2000 example database included with OpenEdge installations.
The examples below use the sports2000.customer table which has the following indexes:

Index Name   Index     Columns             Unique
------------ --------- ------------------- ----------
Cust-Num     (Primary) CustNum             Yes
Name                   Name                No
Sales-Rep              Sales-Rep           No
Country-Post           Country,Postal-code No
Comments    Word Index  Comments



When you use the USE-INDEX option in the record-phrase, Progress will use the index specified instead of those the compiler might have chosen.

Example 1: Index Used: Custnum

The specified index custnum will be used.

FIND customer WHERE custnum = 45 USE-INDEX custnum.

 Example 2: Index Used: Name

While the specified index name will be used, it is not the best index to use
Since the WHERE clause is using custnum, the net result will be bracket on the whole index name.
This statement will result in a full index scan to retrieve the row.

FIND customer WHERE custnum = 45 USE-INDEX name.

For each index in the table, Progress looks at each index component in turn and counts the number of active equality, range, and sort matches. Progress ignores the counts for any components of an index that occur after a component that has no active equality match. Progress compares the results of this count and selects the best index. Progress uses the following order to determine the better of any two indexes.

a. If one index is unique and all of its components are involved in active equality matches and the other index is not unique, or if not all of its components are involved in active equality matches, Progress chooses the former of the two.
b. Select the index with more active equality matches.
c. Select the index with more active range matches.
d. Select the index with more active sort matches.
e. Select the index that is the primary index.
f. Select the first index alphabetically by index name.

If you specify the -v6q startup parameter, Progress might have to scan all the records in the index to find those meeting the conditions, or Progress might have to examine only a subset of the records. This latter case is called bracketing the index and results in more efficient access. Having selected an index as previously described, Progress examines each component as follows to see if the index can be bracketed:

If the component has an active equality match, Progress can bracket it, and it examines the next component for possible bracketing.

If the component has an active range match, Progress can bracket it,but it does not examine the remaining components for possible bracketing.

If the component does not have an active equality match or an active range match, Progress does not examine the remaining components for bracketing.

If you specify the v6q parameter, any conditions you specify in the record-phrase that are not involved in bracketing the selected index are applied to the fields in the record itself to determine if the record meets the overall record-phrase criteria.

References to Other Documentation:

OpenEdge Web Paper: ABL Database Triggers and Indexes, Finding out which indexes are used
Last Modified Date11/21/2018 2:11 PM