All examples in this Article assume a connection to the
sports2000 sample database included with OpenEdge installations.
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:
/* 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.
How indexes are used:
- The client opens a new index cursor or uses an already open one.
- It then sends the server a request that includes the cursor identifier and the bracket range, which are the low-key and high-key values, and asks for the next, previous, first or last rows in the bracket.
- Once a row is retrieved using the index brackets, further selection process continues by applying any remaining selection conditions specified in the WHERE clause.
It is important to note that there are some selection operations that the server cannot do, either because they require access to program variables on the client side, when the fields involved are not indexed, or because they are not implemented on the server side, such as CAN-FIND. In such cases the server will send the rows in the bracket to the client and the client will apply the remaining selection conditions.
Which indexes are used:The OpenEdge compiler/AVM follow a set of rules to identify which indexes would be the best match to support a query. These are described in more detail in the following Articles:
For static queries/FINDs this is established at
compile time.
For dynamic queries, this is established at
runtime when the query is prepared. For dynamic FINDs this is established at runtime when the FIND-* methods are invoked.
How to expose which indexes are used:1. Use XREFWhich indexes will be used by a particular query can be found by examining the output produced by the cross-reference (XREF) option of the compiler.
2. -clientlogging with the -logentrytype QRYINFOThe following Article outlines QRYINFO logging using a dynamic query:
