Kbase P8794: Why do a FIND and a FOR EACH choose different indexes using the same search criteria
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
Why do a FIND, a FOR EACH and an OPEN QUERY choose different indexes using the same search criteria
FACT(s) (Environment):
Progress 8.x
Progress 9.x
FIX:
The FIND statement can only use one index when retrieving records. On the other hand, FOR EACH and OPEN QUERY can use multiple indexes. As a consequence Progress uses two different algorithm for the FIND and the FOR EACH statements in order to choose which index to use by default (i.e. when the USE-INDEX option is not specified).
When only one index satisfies the search criteria, the two algorithms will pick the same one.
When the search criteria matches two or more indexes, instead, it's not unusual that the choice will be different: for example the FIND statement will try to choose a unique index, but the FOR EACH and the OPEN QUERY may well prefer non-unique indexes.
To show this against the sports2000 database, you can write and COMPILE the following example with the XREF option.
FIND FIRST order WHERE order.custNum = 1 AND
order.orderDate = TODAY.
FOR EACH order WHERE order.custNum = 1 AND
order.orderDate = TODAY.
END.
In the given example, for both statements Progress has a choice between index CustOrder (unique, components: custNum and orderNum) and index OrderDate (non-unique, components: orderDate).
In the XREF listing, you'll see that the FIND has chosen index CustOrder, while the FOR EACH has chosen index OrderDate.