Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## Sample Queries

> This appendix contains sample queries and the information that the DataServer provides when you specify the`QUERY-TUNING(DEBUG SQL)` option. In each case, notes explain the DataServer and cursor behavior. The numbers in angle brackets (<`n`>) indicate cursor handles for SQL statements.

> ##### Query 1

>     FIND customer 2.
>     DISPLAY name cust-num postal-code.

>     FIND NEXT customer.
>     DISPLAY name cust-num postal-code.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>        SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 53596
>        SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call omru   <3>
>      OCI call OCIStmtPrepare <4>     sqlcrc = 3562
>         SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM > :p1)) order by CUST_NUM
>      OCI call OCIStmtExecute <4>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <4>

>           Cursor <4> Rows processed 20 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>           Total Rows processed     1 (previous executions)
>      OCI call OCIHandleFree <0>

>           Cursor <3> Rows processed 1 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <3,4> The `WHERE` clause generated by the DataServer positions the cursor after the row retrieved by the first use of cursor <2> to retrieve `CUSTOMER 2`.

> ##### Query 2

>     FIND customer 2.
>     DISPLAY name cust-num postal-code.
>     FIND NEXT customer USE-INDEX Country-Post.

>     DISPLAY name cust-num postal-code WITH FRAME b.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 53596
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <3>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIHandleAlloc <0>
>      OCI call omru   <4>
>      OCI call OCIStmtPrepare <5>     sqlcrc = 49589
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((upper(COUNTRY) = upper(:p1) AND
>           upper(POSTAL_CODE) = upper(:p2) AND PROGRESS_RECID > :p0) OR
>           (upper(COUNTRY) = upper(:p1) AND upper(POSTAL_CODE) > upper(:p2)) OR
>           (upper(COUNTRY) > upper(:p1))) order by upper(COUNTRY),
>           upper(POSTAL_CODE), PROGRESS_RECID
>      OCI call OCIStmtExecute <5>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <5>

>           Cursor <5> Rows processed 20 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>           Total Rows processed     1 (previous executions)
>      OCI call OCIHandleFree <0>

>           Cursor <4> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <3> Rows processed 1 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <2> The DataServer uses this cursor for customer 2.

> <5> The `WHERE` clause generated by the DataServer positions the cursor for `country-post` after `CUSTOMER` `2`. The `ORDER` `BY` clause uses the `PROGRESS_RECID` column as the final component to guarantee unique ordering.

> ##### Query 3

>     FIND customer 2.
>     DISPLAY name cust-num postal-code.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ *
>           FROM DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 53596
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 1 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <3> This cursor selects the `PROGRESS_RECID` column for a particular row by `CUST_NUM`.

> ##### Query 4

>     FIND FIRST customer.
>     DISPLAY name cust-num postal-code.
>     FIND customer 2.
>     DISPLAY name cust-num postal-code.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 1858
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 order by CUST_NUM
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <3>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <4>     sqlcrc = 53596
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID
>           FROM DOCTEST.CUSTOMER T0 WHERE ((CUST_NUM = :1))  order by CUST_NUM
>      OCI call OCIStmtExecute <4>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <4>

>           Cursor <4> Rows processed 1 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>           Total Rows processed     1 (previous executions)
>      OCI call OCIHandleFree <0>

>           Cursor <3> Rows processed 20 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <3> The DataServer executes the `FIND` `FIRST` `CUSTOMER`.

> <4> The DataServer retrieves `CUSTOMER` `2`.

> ##### Query 5

>     FOR EACH customer FIELDS (name cust-num postal-code):
>         DISPLAY name cust-num postal-code.
>     END.

>     OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>          USTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 60425
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un
>          ique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE,POSTA
>          L_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUN
>          T,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0
>      OCI call OCIStmtExecute <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 85 (last execution)
>           Number of array fetches  5
>           Number of rows fetched   85
>           Number of array rows     17
>           Number of array columns  18
>           Number of tables         1
>           Space for one row        472
>           Requested cache size     8192
>           Actual cache size used   8024
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <3> The single lookahead cursor selects columns directly. It ignores the field list because the `FOR` `EACH` loop defaults to a `SHARE-LOCK`. Also, since `FOR` `EACH` loops do not guarantee order of retrieval, the DataServer has not added an `ORDER` `BY` clause. The DataServer called `OCIStmtFetch` to fetch an array of rows. The DataServer used the default cache-size of 8192. Since 472 bytes are required for each row, it used 8024 bytes of cache to fetch up to 17 rows each call. Processing the 85 rows in the `CUSTOMER` table required a total of 5 array fetches.

> ##### Query 6

>     FOR EACH customer NO-LOCK:
>         DISPLAY name cust-num postal-code.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>          USTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 60425
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un
>          ique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE,POSTA
>          L_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUN
>          T,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0
>      OCI call OCIStmtExecute <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call OCIStmtFetch <3>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 85 (last execution)
>           Number of array fetches  5
>           Number of rows fetched   85
>           Number of array rows     17
>           Number of array columns  18
>           Number of tables         1
>           Space for one row        472
>           Requested cache size     8192
>           Actual cache size used   8024
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <3> The single lookahead cursor selects columns directly. It selects all columns because the query does not contain a field list.

> ##### Query 7

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK:
>         DISPLAY name cust-num postal-code.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>          USTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 40887
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un
>          ique_id_0,CUST_NUM,NAME,POSTAL_CODE FROM DOCTEST.CUSTOMER T0
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 85 (last execution)
>           Number of array fetches  1
>           Number of rows fetched   85
>           Number of array rows     105
>           Number of array columns  4
>           Number of tables         1
>           Space for one row        78
>           Requested cache size     8192
>           Actual cache size used   8190
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <3> The cursor selects only the fields in the field-list. The default cache-size of 8192 is sufficient to hold 106 rows. A single fetch retrieves the entire `CUSTOMER` table.

> ##### Query 8

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK
>         QUERY-TUNING (NO-LOOKAHEAD):
>         DISPLAY name cust-num postal-code.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 57261
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID
>           unique_id_0 FROM DOCTEST.CUSTOMER T0
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <2>
>           < repeat for each row >
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 85 (last execution)
>           Number of array fetches  1
>           Number of rows fetched   85
>           Number of array rows     256
>           Number of array columns  1
>           Number of tables         1
>           Space for one row        4
>           Requested cache size     1024
>           Actual cache size used   1024
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>           Total Rows processed     84 (previous executions)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <3> This is a standard cursor. The default cache size is 1024. Since the DataServer fetches only the `PROGRESS_RECID` column, it requires only 4 bytes for each row. A single fetch retrieves all 85 `PROGRESS_RECID` values in the `CUSTOMER` table.

> ##### Query 9

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK
>         QUERY-TUNING (NO-LOOKAHEAD):
>         DISPLAY name cust-num postal-code.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>          USTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 57261
>          SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un
>          ique_id_0 FROM DOCTEST.CUSTOMER T0
>      OCI call OCIStmtExecute <3>
>      OCI call omru   <2>
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <2>
>            < repeat for each row >
>      OCI call OCIStmtExecute <2>
>      OCI call omru   <3>

>           Cursor <3> Rows processed 85 (last execution)
>           Number of array fetches  1
>           Number of rows fetched   85
>           Number of array rows     256
>           Number of array columns  1
>           Number of tables         1
>           Space for one row        4
>           Requested cache size     1024
>           Actual cache size used   1024
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 1 (last execution)
>           Total Rows processed     84 (previous executions)

> <2> The DataServer uses the cursor to compare schema information and fetch column values.

> <3> This is a standard cursor. Note that the advantage of using a field list is lost by not using a lookahead cursor. The DataServer uses the schema comparison cursor to retrieve column values by the `PROGRESS_RECID` column. Only those fields mentioned in the field list are available to the client.

> ##### Query 10

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
>         EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK:
>         DISPLAY customer.name customer.cust-num customer.postal-code
>             order.order-date order.sales-rep.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>          USTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 60664
>          SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORD
>          ER_ T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <3>
>      OCI call omru   <3>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <4>     sqlcrc = 51753
>          SELECT T0.PROGRESS_RECID unique_id_0,T0.CUST_NUM,T0.NAME,T0.POSTA
>          L_CODE,T1.PROGRESS_RECID unique_id_1,T1.ORDER_DATE,T1.SALES_REP F
>          ROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1 WHERE (T1.CUST_NUM = T0
>          .CUST_NUM)
>      OCI call OCIStmtExecute <4>
>      OCI call OCIStmtFetch <4>
>      OCI call OCIStmtFetch <4>
>      OCI call omru   <4>

>           Cursor <4> Rows processed 207 (last execution)
>           Number of array fetches  3
>           Number of rows fetched   207
>           Number of array rows     87
>           Number of array columns  7
>           Number of tables         2
>           Space for one row        94
>           Requested cache size     8192
>           Actual cache size used   8178
>      OCI call OCIHandleFree <0>

>           Cursor <3> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

>           Cursor <2> Rows processed 0 (last execution)
>      OCI call OCIHandleFree <0>

> <2> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <3> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <4> The cursor is used to perform the join by the SQLDB. Since the query specifies `NO-LOCK`, this cursor selects the fields in the field list in addition to those that the client requires (`T0.PROGRESS_RECID`, `T1.PROGRESS_RECID`, `T1.CUST_NUM`). With the default cache size of 8192, processing the entire join requires 3 array fetches.

> ##### Query 11

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
>         EACH order FIELDS (order-date sales-rep) OF customer SHARE-LOCK:
>         DISPLAY customer.name customer.cust-num customer.postal-code
>             order.order-date order.sales-rep.
>     END.

>      OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>          SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <2>
>      OCI call omru   <2>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <3>     sqlcrc = 60664
>          SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM
>           DOCTEST.ORDER_ T0 WHERE PROGRESS_RECID = :rid
>      OCI call OCIStmtExecute-DescribeOnly <3>
>      OCI call omru   <3>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <4>     sqlcrc = 51753
>          SELECT T0.PROGRESS_RECID unique_id_0,T0.CUST_NUM,T0.NAME,
>           T0.POSTAL_CODE,T1.PROGRESS_RECID unique_id_1,T1.ORDER_DATE,
>           T1.SALES_REP FROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1 WHERE
>           (T1.CUST_NUM = T0.CUST_NUM)
>      OCI call OCIStmtExecute <4>
>      OCI call OCIStmtFetch <4>
>      OCI call OCIStmtFetch <4>
>      OCI call OCIHandleAlloc <0>
>      OCI call OCIStmtPrepare <5>     sqlcrc = 18715
>          SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ ORDER_NUM,CUST_NUM
>          ,TO_CHAR(ORDER_DATE,'YYYYMMDDHH24MISS'),TO_CHAR(SHIP_DATE,'YYYYMM
>          DDHH24MISS'),TO_CHAR(PROMISE_DATE,'YYYYMMDDHH24MISS'),CARRIER,INS
>          TRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID  FROM DOCTEST.ORDER_
>          T0 WHERE PROGRESS_RECID = :rid
>      OCI call omru   <5>
>      OCI call OCIStmtExecute <5>
>     	< repeat for each row >
>      OCI call OCIStmtExecute <5>
>      OCI call omru   <4>

> <2> The DataServer uses the cursor to compare schema information. It does not fetch any column values.

> <3> The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the `ORDER_` table contains a `date`, the DataServer does not reuse this cursor to fetch column values.

> <4> The cursor is used to perform the join by the SQLDB. The join still requires a lookahead cursor.

> <5> Since the query requests the `ORDER_` row with a `SHARE-LOCK`, the DataServer must refetch each `ORDER_` row to get all the columns. If the `ORDER_` table did not have a record identifier (`PROGRESS_RECID` in this case), this query would fail. If you must retrieve the `ORDER_` row with a `SHARE-LOCK`, removing the field list eliminates the need to refetch the row.

> ##### Query 12

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
>         EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK
>         QUERY-TUNING (NO-LOOKAHEAD):
>         DISPLAY customer.name customer.cust-num customer.postal-code
>             order.order-date order.sales-rep.
>     END.
>     OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>         SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM
>           DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid
>     OCI call OCIStmtExecute-DescribeOnly <2>
>     OCI call omru   <2>
>     OCI call OCIStmtPrepare <3>     sqlcrc = 60664
>         SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM
>           DOCTEST.ORDER_ T0 WHERE PROGRESS_RECID = :rid
>     OCI call OCIStmtExecute-DescribeOnly <3>
>     OCI call omru   <3>
>     OCI call OCIStmtPrepare <4>     sqlcrc = 48763
>         SELECT T0.PROGRESS_RECID unique_id_0,T1.PROGRESS_RECID
>           unique_id_1 FROM DOCTEST.CUSTOMER T0,DOCTEST.ORDER_ T1
>           WHERE (T1.CUST_NUM = T0.CUST_NUM)
>     OCI call OCIStmtExecute <4>
>     OCI call omru   <2>
>     OCI call OCIStmtExecute <2>
>     OCI call OCIStmtPrepare <5>     sqlcrc = 18715
>         SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ ORDER_NUM,CUST_NUM
>         ,TO_CHAR(ORDER_DATE,'YYYYMMDDHH24MISS'),TO_CHAR(SHIP_DATE,'YYYYMM
>         DDHH24MISS'),TO_CHAR(PROMISE_DATE,'YYYYMMDDHH24MISS'),CARRIER,INS
>         TRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID  FROM DOCTEST.ORDER_
>         T0 WHERE PROGRESS_RECID = :rid
>     OCI call omru   <5>
>     OCI call OCIStmtExecute <5>
>     OCI call omru   <2>
>     OCI call OCIStmtExecute <2>
>           < repeat, alternating handles 5 and 2 >
>          Cursor <4> Rows processed 207 (last execution)
>          Number of array fetches  2
>          Number of rows fetched   207
>          Number of array rows     128
>          Number of array columns  2
>          Number of tables         2
>          Space for one row        8
>          Requested cache size     1024
>          Actual cache size used   1024
>     OCI call OCIHandleFree <0>
>          Cursor <5> Rows processed 1 (last execution)
>          Total Rows processed     206 (previous executions)
>     OCI call OCIHandleFree <0>
>          Cursor <2> Rows processed 1 (last execution)
>          Total Rows processed     203 (previous executions)
>     OCI call OCIHandleFree <0>
>          Cursor <3> Rows processed 0 (last execution)

> <2> The DataServer uses the cursor to compare schema information and fetch `CUSTOMER` rows.

> <3> The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the `ORDER_` table contains a date, the DataServer does not reuse this cursor to fetch column values.

> <4> The cursor is used to perform the join by the SQLDB. It uses a standard cursor for the join. Each row of the join requires 8 bytes of the cache because the join cursor fetches only the unique integer record identifiers.

> <5> The DataServer uses this cursor to fetch `ORDER_` rows by the `PROGRESS_RECID` column. It cannot use the schema comparison cursor (<39>) because the DataServer must perform a `TO_CHAR` conversion on the date columns.

> ##### Query 13

>     FOR EACH customer FIELDS (name cust-num postal-code) NO-LOCK,
>         EACH order FIELDS (order-date sales-rep) OF customer NO-LOCK
>         QUERY-TUNING (NO-JOIN-BY-SQLDB):
>         DISPLAY customer.name customer.cust-num customer.postal-code
>             order.order-date order.sales-rep.
>     END.

>     OCI call OCIStmtPrepare <2>     sqlcrc = 41633
>         SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.C
>         USTOMER T0 WHERE PROGRESS_RECID = :rid
>     OCI call OCIStmtExecute-DescribeOnly <2>
>     OCI call omru   <2>
>     OCI call OCIStmtPrepare <3>     sqlcrc = 60664
>         SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORD
>         ER_ T0 WHERE PROGRESS_RECID = :rid
>     OCI call OCIStmtExecute-DescribeOnly <3>
>     OCI call omru   <3>
>     OCI call OCIStmtPrepare <4>     sqlcrc = 40887
>         SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM)  */ PROGRESS_RECID un
>         ique_id_0,CUST_NUM,NAME,POSTAL_CODE FROM DOCTEST.CUSTOMER T0
>     OCI call OCIStmtExecute <4>
>     OCI call OCIStmtPrepare <5>     sqlcrc = 17552
>         SELECT /*+ INDEX_ASC(T0 ORDER_##CUST_ORDER)  */ PROGRESS_RECID un
>         ique_id_0,CUST_NUM,ORDER_DATE,SALES_REP FROM DOCTEST.ORDER_ T0 WH
>         ERE (CUST_NUM = :1)
>     OCI call OCIStmtExecute <5>
>     	< repeat for each row >
>          Cursor <4> Rows processed 85 (last execution)
>          Number of array fetches  1
>          Number of rows fetched   85
>          Number of array rows     105
>          Number of array columns  4
>          Number of tables         1
>          Space for one row        78
>          Requested cache size     8192
>          Actual cache size used   8190
>     OCI call OCIHandleFree <0>
>          Cursor <5> Rows processed 0 (last execution)
>          Total Rows processed     207 (previous executions)
>          Number of array fetches  0
>          Number of rows fetched   0
>          Number of array rows     141
>          Number of array columns  4
>          Number of tables         1
>          Space for one row        58
>          Requested cache size     8192
>          Actual cache size used   8178

> <2> The DataServer uses the cursor to compare schema information and fetch `CUSTOMER` rows.

> <3> The DataServer uses the cursor to compare schema information. It does not fetch any column values. Note that because the `ORDER_` table contains a date, the DataServer does not reuse this cursor to fetch column values.

> <4> The DataServer uses a lookahead cursor to select fields in the field list in addition to those required by the client.

> <5> The lookahead cursor selects fields from the `ORDER_` table that correspond to a particular `CUSTOMER` row (`WHERE` `CUST_NUM` `=` `:1`).

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