Kbase 21097: Using USE-INDEX Explained
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/11/2010 |
|
Status: Verified
GOAL:
Using USE-INDEX Explained
GOAL:
How does Progress chooses an index
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)
FIX:
The following section describes the index selection process.
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 Comments Word Index
Using USE-INDEX
When you use the USE-INDEX option, Progress will use the specified index instead of those the compiler might have chosen.
FIND customer WHERE custnum = 45 USE-INDEX custnum.
Index Used: Custnum
The specified index custnum will be used.
FIND customer WHERE custnum = 45 USE-INDEX name.
Index Used: Name
The specified index name will be used. 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.
If you use the USE-INDEX option in the record-phrase, Progress uses the index you name in that option.
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.