To retrieve rows, OpenEdge relies on
indexes and
index brackets to identify which records should be fetched.
The only exception to this is when a FIND by a ROWID / RECID value will directly fetch a record by it's internal record identifier without using indexes.
Such operations can be recognised by the WHERE clause on the buffer including a call to ROWID/RECID function for the same buffer, example:
/* rowidinvar.p - store a ROWID in a variable */
DEFINE VARIABLE rowid-customer AS ROWID NO-UNDO.
FIND LAST customer.
rowid-customer = ROWID(customer).
RELEASE customer.
/* re-find record based on stored ROWID */
FIND customer WHERE ROWID(customer) = rowid-customer.
DISPLAY customer.custnum customer.name.
Compile the above with XREF to prove that an index is not being used when the stored ROWID is used:
COMPILE rowidinvar.p XREF rowidinvar.txt.
The first FIND LAST CUSTOMER uses the (primary) CustNum INDEX:
SEARCH <ldbname>.Customer CustNum WHOLE-INDEX
The FIND based on the stored ROWID does not use an index:
SEARCH <ldbname>.Customer RECID
The ROWID value must be passed to not use an index. For example
FOR EACH Customer WHERE RECID (Customer) LE 1024:
DISP Customer.NAME.
END.
The FOR EACH uses the (primary) CustNum INDEX to find RECID's LE 1024:
SEARCH <ldbname>.Customer CustNum WHOLE-INDEX
In order to not use an index to find RECID's in range, a variable needs to be used. For example:
DEFINE VARIABLE i AS INT64 NO-UNDO.
FIND _file WHERE _file-name = "Customer" NO-LOCK.
DO i = 96 TO 1024.
FIND Customer NO-LOCK WHERE RECID(Customer) = i NO-ERROR.
IF AVAILABLE Customer AND i <> INTEGER(_file._Template) THEN
DISP RECID(Customer) NAME.
END.
The first FIND uses the _File _File-Name Index to initialise the query
SEARCH <ldbname>._File _File-Name
The FIND based on the ROWID in the variable, does not use an index:
SEARCH <ldbname>.Customer RECID
