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