SQL Processing - Relational Database Index Design and the Optimizers - page 43

the program does not issue CLOSE CURSOR or if a stronger isolation level is
specified, the DBMS releases the locks at commit point.
Query tools generate DECLARE CURSOR, OPEN CURSOR, a FETCH
loop, and CLOSE CURSOR from the SELECT statement.
SQL application programming courses have been known to claim that OPEN
CURSOR always creates the result table, defined by DECLARE CURSOR with
the values moved into the host variables, at the time of the OPEN CURSOR call.
Fortunately, this statement is not true . To avoid unnecessary work, the DBMS
materializes result rows as late as possible . The chosen materialization time may
also affect the contents of the result: if the DBMS chooses early materialization ,
the FETCH calls retrieve result rows from a temporary table; the DBMS does
not update this temporary table when the database is updated.
To illustrate the significance of the time of materialization, we will use the
program shown in SQL 3.6, which reads a customer's last invoice.
The response to the query is always a single row. The program will issue
only one FETCH call, hence the request to use the most efficient access path
for 1 row. The actual syntax is DBMS specific as we saw earlier; whenever we
want to use this function throughout this topic, we will use this “generic form” to
avoid the necessity of providing multiple variations of the code. Despite making
the one row request, what does the DBMS actually read?
1. The whole result table as defined by the cursor ( all the customer's

invoices)?
2. A single row relating to the required invoice?

SQL 3.6

DECLARE LASTINV CURSOR FOR
SELECT INO, IDATE, IEUR
FROM INVOICE
WHERE CNO = :CNO
ORDER BY INO DESC
WE WANT 1 ROW PLEASE

OPEN CURSOR LASTINV
FETCH CURSOR LASTINV
CLOSE CURSOR LASTINV

From the performance point of view, this may be a critical question . We will
consider the two possible alternatives in turn.

Alternative 1: FETCH Call Materializes One Result Row

The DBMS chooses this desirable alternative if
ž There is no sequence requirement (ORDER BY, GROUP BY, etc.) or