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

MATERIALIZING THE RESULT ROWS

Materializing the result rows means performing the database accesses required
to build the result set . In the best case, this simply requires a row to be moved
from the database buffer pool to the program. In the worst case, the DBMS will
request a large number of disk reads.
When a single row is retrieved with a singleton SELECT, there is no choice
but to materialize the result row when the SELECT call is executed. On the other
hand, when a cursor is used, which is necessary if there may be several result
rows, there are two alternatives as shown in Figure 3.9:
1. The DBMS materializes the whole result table at OPEN CURSOR (or at
least at the first FETCH).
2. Each FETCH materializes one result row.
We will discuss each of these possibilities in turn.

Cursor Review

A FETCH call moves one row of the result table defined by the DECLARE
CURSOR statement to the application program. When DECLARE CURSOR
contains host variables , at execution time the application program moves the
actual values into the host variables before the OPEN CURSOR call. If the
application program creates several result tables using the same cursor, a CLOSE
CURSOR call must first be issued. New values may be moved into the host
variables and the cursor is reopened with an OPEN CURSOR call.
The CLOSE CURSOR call releases any locks that may have been taken
by the last FETCH call, assuming the customary isolation level (ANSI SQL-
92: READ COMMITTED, Level 1, e.g., CURSOR STABILITY with DB2). If

DECLARE IM CURSOR...

OPEN CURSOR IM
FETCH CURSOR IM ---- while found
CLOSE CURSOR IM

If DBMS does not
sort result rows

OR

OPEN CURSOR:
All result rows

FETCH:
One result row

Figure 3.9 Result row materialization.