Using USE-INDEX
When USE-INDEX is specified, it gives a very clear instruction to the compiler which index to select.
- USE-INDEX overrides/bypasses any other index selection and forces the compiler to use a specific index instead of those the compiler will have otherwise chosen.
- The compiler does not need to evaluate an index to use, as the developer has explicitly provided one for the required sorting and bracketing needed by specifying USE-INDEX.
- The -noinactiveidx startup parameter tells the compiler not to use an inactive index when it evaluates the index to use for a query, unless USE-INDEX is specified.
Version 6 Query (-v6q) vs USE-INDEX
The ABL can be forced to use only one index by specifying the USE-INDEX option or by using the -v6q parameter
When USE-INDEX is used, the compiler does not need to evaluate an index to use,
The -v6q startup parameter, may require scanning all the records in the index to find those meeting the conditions, or Progress might have to examine only a subset of the records.
The latter case is called bracketing the index and results in more efficient access. Having selected an index according to the bracketing rules, 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.
Example: USE-INDEX
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
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 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.
