NOTE: Although OpenEdge documentation related to single index selection may give the impression that the WHERE clause determines the index to be selected. That is not true because the query type of the statement and possibly other internal factors do play roles in that selection process.
OpenEdge documentation bug PSC00312958 was logged to stress that even when the WHERE clause is the same, different query statements (FIND, FOR, Open QUERY, etc) may select different indexes.
Single Index Selection:When a query does not use ROWID or USE-INDEX, a single index or multiple indexes may be used to retrieve the requested data, based on the conditions specified in the WHERE or BY clause. The following rules are used by the compiler for single index retrievals:
1) Select an index which is unique and all the components are involved in the equality matches.
2) Select the index with more active equality matches.
3) Select the index with more active range matches.
4) Select the index with more active sort matches.
5) Select the index that is the primary index.
6) Select the first index alphabetically by index name.
Example 1:
FOR EACH customer:
END.
Index Used: Custnum
Bracket on the whole index Custnum. The primary index Custnum is used since there is no WHERE clause.
Example 2:
FOR EACH customer WHERE state = "MD":
END.
Index Used: Custnum
Bracket on the whole index Custnum. The primary index Custnum is used since there is no index on State. This will result in a full index scan to retrieve the rows.
Example 3:
FIND customer WHERE custnum = 12.
Index Used: Custnum
Single bracket on one index.
Example 4:
FOR EACH customer WHERE salesrep = "John":
END.
Index Used: Salesrep
Single bracket on one index.
Example 5:
FOR EACH customer WHERE salesrep BEGINS "J":
END.
Index Used: Salesrep
Single bracket on one index.
Example 6:
FOR EACH customer WHERE custnum > 20 AND custnum < 40:
END.
Index Used: Custnum
Single bracket on one index.
Example 7:
FOR EACH customer WHERE custnum > 56 BY name:
END.
Index Used: Custnum
Single bracket on Custnum index. The index on name will not be used for sorting, instead internal sorting will be done once the rows are retrieved.
Example 8:
FOR EACH customer BY name:
END.
Index Used: Name
Bracket on the whole index Name.
Example 9:If a function or expression is used for the components of an index, an index or bracket will not be used.
FOR EACH customer WHERE SUBSTRING(name,1,1) = "A":
END.
Index Used: Custnum
The index on Name will not be used, instead primary index on Custnum will be used. This expression will result in a full index scan to retrieve the rows.
Example 10:
FOR EACH customer WHERE (IF rowid-customer <> ? THEN ROWID(customer) = rowid-customer ELSE true):
END.
Index Used: Custnum
In this case row will not be retrieved directly using the rowid. Because Progress selects the index at compile time, it will not be able to evaluate the IF statement. Therefore the primary index on Custnum will be selected, resulting in full index scan to retrieve the row.
Example 11:
FOR EACH customer WHERE name MATCHES "A*":
END.
Index Used: Custnum
The index on Name will not be used, instead primary index on Custnum will be used. This expression will result in a full index scan to retrieve the rows.
An exception to these rules are where there are two indexes that have exactly the same components (See below).
Where there are two 'equivalent' indexes with the same components:When there are multiple equivalent indexes that use the same fields and could both satisfy the query, there is no advantage in one being chosen over the other.
In this case, if one of the indexes is the primary index it is chosen otherwise the last index name alphabetically in the list of index names is chosen by the compiler
For example, a table has the following 2 indexes:
Main (Primary Unique)
Company – Character
PartNum – Character
Reference - Character
BinNum – Integer
StorageArea (Unique)
Company – Character
BinNum – Integer
PartNum – Character
Reference – Character
With the following WHERE clause, the compiler would choose the Main index because it is the Primary index.
FIND FIRST <tablename> WHERE
<tablename>.Company = '':U AND
<tablename>.PartNum = '':U AND
<tablename>.Reference = '':U AND
<tablename>.BinNum = 0
NO-LOCK NO-ERROR.
Where there are two UNIQUE indexes with different components that could satisfy the query:When multiple unique indexes can be chosen, the AVM picks the last index found that fulfils the criteria and uses it.
For example, a table has the following 2 unique indexes:
ord_Id (Primary Unique)
ord_Id - Integer
ord_InternalId (Unique)
ord_InternalId – Character
With the following WHERE clause, the compiler would choose the ord_InternalId index because it is the last index found.
FOR EACH tt_Order WHERE ord_Id = 1 AND ord_InternalId = '1' NO-LOCK:
END.
If the order of the indexes is changed (e.g. ord_InternalId (Unique) is defined before ord_Id (Primary Unique) in a temp-table) then the ord_Id index is selected.
