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

P F,C

STAR T

INO CNO

20 MT

20 KT

P,C INVOICE

CNO CCTRY

20,000,000 rows

20 KT

FF(IEUR > :IEUR)
= 0.1 %

CUST

1,000,000 rows

Figure 8.5 Program B with current indexes.

the country of the customer by reading the corresponding CUST row via the
primary key index CNO.

ProgramB:OuterTableINVOICE

SQL 8.7

Program B

DECLARE CURSORI CURSOR FOR
SELECT CNO, INO, IEUR

FROM INVOICE

WHERE IEUR > :IEUR

ORDER BY IEUR DESC

OPEN CURSORI
DO

FETCH CURSORI while IEUR > :IEUR

SELECT CNAME, CTYPE

FROM CUST

WHERE CNO = :CNO

AND
CCTRY = :CCTRY

DO END
CLOSE CURSORI