Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## Field lists

> 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 (cust-num 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 (cust-num 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 the Appendix C, "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 zip = 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: Progress 4GL Reference for more information on the `FIELDS` option.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095