Progress
DataServer
for ORACLE Guide


Sample Queries

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

Query 1

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

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

OCI call oopen  <1> cc = 0
 OCI call osql3  <1>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <2> cc = 1
 OCI call osql3  <2>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0 WHERE (CUST_NUM = :1)  ORDER BY CUST_NUM 
 OCI call oexec  <2>
 OCI call oexec  <1>
 OCI call osql3  <2>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0 WHERE ((CUST_NUM > :p1)) ORDER BY CUST_NUM 
 OCI call oexec  <2>
 OCI call oexec  <1>
 
      Cursor <1> Rows processed 1
 OCI call oclose <1> cc = 2
 
      Cursor <2> Rows processed 81
 OCI call oclose <2> cc = 1 

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

<2>    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 oopen  <3> cc = 0
 OCI call osql3  <3>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <4> cc = 1
 OCI call osql3  <4>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0 WHERE (CUST_NUM = :1)  ORDER BY CUST_NUM 
 OCI call oexec  <4>
 OCI call oexec  <3>
 OCI call oopen  <5> cc = 2
 OCI call osql3  <5>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST) */ PROGRESS_RECID
      FROM SPORTS.CUSTOMER T0 WHERE ((U##COUNTRY = :p1 AND U##POSTAL_C
     ODE = :p2 AND PROGRESS_RECID > :p0) OR (U##COUNTRY = :p1 AND U##P
     OSTAL_CODE > :p2) OR (U##COUNTRY > :p1)) ORDER BY U##COUNTRY, U##
     POSTAL_CODE, PROGRESS_RECID 
 OCI call oexec  <5>
 OCI call oexec  <3>
 
      Cursor <3> Rows processed 1
 OCI call oclose <3> cc = 3
 
      Cursor <5> Rows processed 62
 OCI call oclose <5> cc = 2
 
      Cursor <4> Rows processed 1
 OCI call oclose <4> cc = 1 

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

<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 oopen  <6> cc = 0
 OCI call osql3  <6>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <7> cc = 1
 OCI call osql3  <7>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0 WHERE (CUST_NUM = :1)  ORDER BY CUST_NUM 
 OCI call oexec  <7>
 OCI call oexec  <6>
 
      Cursor <6> Rows processed 1
 OCI call oclose <6> cc = 2
 
      Cursor <7> Rows processed 1
 OCI call oclose <7> cc = 1 

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

<7>    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 oopen  <8> cc = 0
 OCI call osql3  <8>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <9> cc = 1
 OCI call osql3  <9>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0 ORDER BY CUST_NUM 
 OCI call oexec  <9>
 OCI call oexec  <8>
 OCI call oopen  <10> cc = 2
 OCI call osql3  <10>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST) */ PROGRESS_RECID
      FROM SPORTS.CUSTOMER T0 WHERE ((U##COUNTRY = :p1 AND U##POSTAL_C
     ODE = :p2 AND PROGRESS_RECID > :p0) OR (U##COUNTRY = :p1 AND U##P
     OSTAL_CODE > :p2) OR (U##COUNTRY > :p1)) ORDER BY U##COUNTRY, U##
     POSTAL_CODE, PROGRESS_RECID 
 OCI call oexec  <10>
 OCI call oexec  <8>
 
      Cursor <8> Rows processed 1
 OCI call oclose <8> cc = 3
 
      Cursor <10> Rows processed 31
 OCI call oclose <10> cc = 2
 
      Cursor <9> Rows processed 83
 OCI call oclose <9> cc = 1 

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

<10>   The WHERE clause generated by the DataServer positions the cursor after the row retrieved by the first use of cursor <8> to retrieve CUSTOMER 2. Unlike the WHERE clause with cursor <5> (Query 2), this non-unique index has two components.

Query 5

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

OCI call oopen  <21> cc = 0
 OCI call osql3  <21>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <22> cc = 1
 OCI call oparse <22>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID uni
     que_int_0,CUST_NUM,U##COUNTRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRES
     S2,CITY,STATE,U##POSTAL_CODE,POSTAL_CODE,CONTACT,PHONE,U##SALES_R
     EP,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUNT,COMMENTS,PROGRES
     S_RECID FROM SPORTS.CUSTOMER T0
 OCI call oexfet <22>
 
      Cursor <21> Rows processed 0
 OCI call oclose <21> cc = 2
 
      Cursor <22> Rows processed 83
      Number of array fetches  6
      Number of rows fetched   83
      Number of array rows     15
      Number of array columns  22
      Number of tables         1
      Space for one row        525
      Requested cache size     8192
      Actual cache size used   7875
 OCI call oclose <22> cc = 1 

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

<22>   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 performed an oexfet to fetch an array of rows. The DataServer used the default cache-size of 8192. Since 525 bytes are required for each row, it used only 7875 bytes of cache to fetch up to 15 rows each call. Processing the 83 rows in the CUSTOMER table required a total of 6 array fetches.

Query 6

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

OCI call oopen  <23> cc = 0
 OCI call osql3  <23>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <24> cc = 1
 OCI call oparse <24>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID uni
     que_int_0,CUST_NUM,U##COUNTRY,COUNTRY,U##NAME,NAME,ADDRESS,ADDRES
     S2,CITY,STATE,U##POSTAL_CODE,POSTAL_CODE,CONTACT,PHONE,U##SALES_R
     EP,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS,DISCOUNT,COMMENTS,PROGRES
     S_RECID FROM SPORTS.CUSTOMER T0
 OCI call oexfet <24>
 
      Cursor <23> Rows processed 0
 OCI call oclose <23> cc = 2
 
      Cursor <24> Rows processed 83
      Number of array fetches  6
      Number of rows fetched   83
      Number of array rows     15
      Number of array columns  22
      Number of tables         1
      Space for one row        525
      Requested cache size     8192
      Actual cache size used   7875
 OCI call oclose <24> cc = 1 

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

<24>   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 oopen  <25> cc = 0
 OCI call osql3  <25>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <26> cc = 1
 OCI call oparse <26>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID uni
     que_int_0,CUST_NUM,NAME,POSTAL_CODE FROM SPORTS.CUSTOMER T0
 OCI call oexfet <26>
 
      Cursor <25> Rows processed 0
 OCI call oclose <25> cc = 2
 
      Cursor <26> Rows processed 83
      Number of array fetches  1
      Number of rows fetched   83
      Number of array rows     106
      Number of array columns  4
      Number of tables         1
      Space for one row        77
      Requested cache size     8192
      Actual cache size used   8162
 OCI call oclose <26> cc = 1 

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

<26>   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 oopen  <27> cc = 0
 OCI call osql3  <27>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <28> cc = 1
 OCI call oparse <28>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0
 OCI call oexfet <28>
 OCI call oexec  <27>   83 times
 
      Cursor <27> Rows processed 1
 OCI call oclose <27> cc = 2
 
      Cursor <28> Rows processed 83
      Number of array fetches  1
      Number of rows fetched   83
      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 oclose <28> cc = 1 

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

<28>   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 83 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 oopen  <29> cc = 0
 OCI call osql3  <29>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <30> cc = 1
 OCI call oparse <30>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID FRO
     M SPORTS.CUSTOMER T0
 OCI call oexfet <30>   83 times
 
      Cursor <29> Rows processed 1
 OCI call oclose <29> cc = 2
 
      Cursor <30> Rows processed 83
      Number of array fetches  1
      Number of rows fetched   83
      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 oclose <30> cc = 1 

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

<30>   This is a standard cursor. Note that the advantage of using a field list is lost by not using a standard 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 oopen  <31> cc = 0
 OCI call osql3  <31>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <32> cc = 1
 OCI call osql3  <32>
     SELECT * FROM SPORTS.ORDER_ WHERE progress_recid = :rid
 OCI call oopen  <33> cc = 2
 OCI call oparse <33>
     SELECT T0.PROGRESS_RECID unique_int_0,T0.CUST_NUM,T0.NAME,T0.POST
     AL_CODE,T1.PROGRESS_RECID unique_int_1,T1.CUST_NUM,TO_CHAR(T1.ORD
     ER_DATE,’YYYYMMDDHH24MISS’),T1.SALES_REP FROM SPORTS.CUSTOMER T0,
     SPORTS.ORDER_ T1 WHERE (T1.CUST_NUM = T0.CUST_NUM)
 OCI call oexfet <33>
          omru   <33>
          omru   <33>
     Cursor <31> Rows processed 0
 OCI call oclose <31> cc = 3
       Cursor <33> Rows processed 207
      Number of array fetches  4
      Number of rows fetched   207
      Number of array rows     57
      Number of array columns  8
      Number of tables         2
      Space for one row        143
      Requested cache size     8192
      Actual cache size used   8151
 OCI call oclose <33> cc = 2
      Cursor <32> Rows processed 0
 OCI call oclose <32> cc = 1 

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

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

<33>   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 4 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 oopen  <34> cc = 0
 OCI call osql3  <34>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <35> cc = 1
 OCI call osql3  <35>
     SELECT * FROM SPORTS.ORDER_ WHERE progress_recid = :rid
 OCI call oopen  <36> cc = 2
 OCI call oparse <36>
     SELECT T0.PROGRESS_RECID unique_int_0,T0.CUST_NUM,T0.NAME,T0.POST
     AL_CODE,T1.PROGRESS_RECID unique_int_1,T1.CUST_NUM,TO_CHAR(T1.ORD
     ER_DATE,’YYYYMMDDHH24MISS’),T1.SALES_REP FROM SPORTS.CUSTOMER T0,
     SPORTS.ORDER_ T1 WHERE (T1.CUST_NUM = T0.CUST_NUM)
 OCI call oexfet <36>
 OCI call oopen  <37> cc = 3
 OCI call osql3  <37>
     SELECT ORDER_NUM,CUST_NUM,TO_CHAR(ORDER_DATE,’YYYYMMDDHH24MISS’),
     TO_CHAR(SHIP_DATE,’YYYYMMDDHH24MISS’),TO_CHAR(PROMISE_DATE,’YYYYM
     MDDHH24MISS’),CARRIER,INSTRUCTIONS,PO,TERMS,U##SALES_REP,SALES_RE
     P,PROGRESS_RECID  FROM SPORTS.ORDER_ WHERE PROGRESS_RECID = :rid
 OCI call oexec  <37>   206 times
 
      Cursor <34> Rows processed 0
 OCI call oclose <34> cc = 4
 
      Cursor <36> Rows processed 207
      Number of array fetches  4
      Number of rows fetched   207
      Number of array rows     57
      Number of array columns  8
      Number of tables         2
      Space for one row        143
      Requested cache size     8192
      Actual cache size used   8151
 OCI call oclose <36> cc = 3
 
      Cursor <37> Rows processed 1
 OCI call oclose <37> cc = 2
 
      Cursor <35> Rows processed 0
 OCI call oclose <35> cc = 1 

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

<35>   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.

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

<37>   Since the query requests the ORDER_ row with a SHARE-LOCK, the DataServer must refetch each ORDER_ row to get all 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 ORDER_ 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 oopen  <38> cc = 0
 OCI call osql3  <38>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <39> cc = 1
 OCI call osql3  <39>
     SELECT * FROM SPORTS.ORDER_ WHERE progress_recid = :rid
 OCI call oopen  <40> cc = 2
 OCI call oparse <40>
     SELECT T0.PROGRESS_RECID,T1.PROGRESS_RECID FROM SPORTS.CUSTOMER T
     0,SPORTS.ORDER_ T1 WHERE (T1.CUST_NUM = T0.CUST_NUM)
 OCI call oexfet <40>
 OCI call oexec  <38>
 OCI call oopen  <41> cc = 3
 OCI call osql3  <41>
     SELECT ORDER_NUM,CUST_NUM,TO_CHAR(ORDER_DATE,’YYYYMMDDHH24MISS’),
     TO_CHAR(SHIP_DATE,’YYYYMMDDHH24MISS’),TO_CHAR(PROMISE_DATE,’YYYYM
     MDDHH24MISS’),CARRIER,INSTRUCTIONS,PO,TERMS,U##SALES_REP,SALES_RE
     P,PROGRESS_RECID  FROM SPORTS.ORDER_ WHERE PROGRESS_RECID = :rid
 OCI call oexec  <41>   204 times
 OCI call oexec  <38>   204 times
      Cursor <38> Rows processed 1
 OCI call oclose <38> cc = 4
      Cursor <40> Rows processed 207
      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 oclose <40> cc = 3
      Cursor <41> Rows processed 1
 OCI call oclose <41> cc = 2
      Cursor <39> Rows processed 0
 OCI call oclose <39> cc = 1 

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

<39>   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.

<40>   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.

<41>   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 oopen  <42> cc = 0
CI call osql3  <42>
     SELECT * FROM SPORTS.CUSTOMER WHERE progress_recid = :rid
 OCI call oopen  <43> cc = 1
 OCI call osql3  <43>
     SELECT * FROM SPORTS.ORDER_ WHERE progress_recid = :rid
 OCI call oopen  <44> cc = 2
 OCI call oparse <44>
     SELECT /*+ INDEX_ASC(T0 CUSTOMER##CUST_NUM) */ PROGRESS_RECID uni
     que_int_0,CUST_NUM,NAME,POSTAL_CODE FROM SPORTS.CUSTOMER T0
 OCI call oexfet <44>
 OCI call oopen  <45> cc = 3
 OCI call oparse <45>
     SELECT /*+ INDEX_ASC(T0 ORDER_##CUST_ORDER) */ PROGRESS_RECID uni
     que_int_0,CUST_NUM,TO_CHAR(ORDER_DATE,’YYYYMMDDHH24MISS’),SALES_R
     EP FROM SPORTS.ORDER_ T0 WHERE CUST_NUM = :1
 OCI call oexfet <45>   82 times
      Cursor <42> Rows processed 0
 OCI call oclose <42> cc = 4
      Cursor <44> Rows processed 83
      Number of array fetches  1
      Number of rows fetched   83
      Number of array rows     106
      Number of array columns  4
      Number of tables         1
      Space for one row        77
      Requested cache size     8192
      Actual cache size used   8162
 OCI call oclose <44> cc = 3
      Cursor <45> Rows processed 4
      Number of array fetches  1
      Number of rows fetched   4
      Number of array rows     124
      Number of array columns  4
      Number of tables         1
      Space for one row        66
      Requested cache size     8192
      Actual cache size used   8184
 OCI call oclose <45> cc = 2
      Cursor <43> Rows processed 0
 OCI call oclose <43> cc = 1 

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

<43>   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.

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

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


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