Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 152

ž Change the program so that it counts the number of FETCH calls issued.
ž Save positioning information in an auxiliary table to enable the next trans-
action to continue from that point.
The SQL changes required for the first transaction are shown in SQL 8.11.
Note the 20 rows please and the SAVE values, and that the invoice number
has been added to the ORDER BY clause in CURSORI; this is necessary for
positioning purposes.
When the next screen is requested, the program must start from the point
defined by the saved values. Let us call these values IEURLAST and INOLAST.
The SQL changes required for the subsequent transactions are shown in SQL
8.12. It is possible that there are two or more invoices with the same invoice total
(column IEUR). Therefore, the program must first open a cursor, CURSORI1,
for any undisplayed invoices with the same invoice value as IEURLAST. When
all the rows for that value have been FETCHed, the first cursor is closed and a
second cursor, CURSORI2, is opened for the next large invoices.

SQL 8.11

Program B+ First transaction
DECLARE CURSORI CURSOR FOR
SELECT CNO, INO, IEUR
FROM INVOICE
WHERE IEUR > :IEUR
ORDER BY IEUR DESC, INO
WE WANT 20 ROWS PLEASE

OPEN CURSORI
DO

FETCH CURSORI max 20 times

SELECT CNAME, CTYPE

FROM CUST

WHERE CNO = :CNO

AND
CCTRY = :CCTRY

DO END
CLOSE CURSORI

SAVE / INSERT IEUR, INO the values of the last line

displayed to the user

How long does it take now to build one screen with the ideal indexes?
Let us again assume the largest filter factors, 10 and 0.1% using SQL 8.12 and
Figure 8.9.

Step 1: Find 20 Large Invoices from Index (IEUR DESC, INO, CNO) It
does not matter how many rows are found by CURSORI1 and by CURSORI2;