The DataServer fully supports field lists in queries (DEFINE QUERY, FOR EACH, PRESELECT, and SQL SELECT statements). For example, the following statement returns the same results for OpenEdge and Oracle databases:
DEFINE QUERY myquery FOR customer FIELDS (custnum name) SCROLLING.
Include the SCROLLING option to enable record prefetch. You must include the NO-LOCK option when you open queries with field lists, as in the following example:
OPEN QUERY myquery NO-LOCK.
Similarly, you must include the NO-LOCK option in FOR EACH statements that include field lists, as in the following example:
FOR EACH customer FIELDS (custnum name) NO-LOCK:
The NO-LOCK option ensures that the DataServer does not have to refetch rows, which might slow performance. In addition, combining lookahead cursors and field lists especially improves a query's performance. See Sample Queries for a comparison of lookahead and standard cursors with field lists.
Use field lists to retrieve only those fields that your application requires. (For performance reasons, the DataServer retrieves the first index field even when you do not include it in the field list. In cases when the DataServer can predict that a query requires a refetch, it retrieves the entire record.) The DataServer allocates memory based on the maximum size specified for a field in a record. Omitting larger fields or unnecessary fields from a query enhances performance.
When you specify a field that has an extent, the query returns the entire array. You can specify an Oracle LONG column in a field list. However, when the query selects a LONG column that has more than 255 bytes of data, the DataServer refetches the column using a row identifier (PROGRESS_RECID column, unique NUMBER index, or native ROWID). In the case of views with aggregates or joins where there is no row identifier, the query stops and you receive an error that the record was truncated.
When the DataServer processes a query with a field list, it caches the fields that are part of the field list and any other fields that the query specified on the client, which you can then access without making another call to the Oracle RDBMS. For example, the DataServer fetches the name and the zip field to process the following query:
FOR EACH customer FIELDS(name) WHERE customer.postalcode = 01730 NO-LOCK:
If you specify a field list in a join, you might have to adjust the cache size for lookahead cursors, either with the CACHE-SIZE option in a QUERY-TUNING phrase, or at the session level with the -Dsrv qt_cache_size startup parameter.
Any performance gained through field lists is lost if you use nonlookahead cursors or SHARE-LOCK.
See the Record Phrase entry in OpenEdge Development: ABL Reference for more information on the FIELDS option.
