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.