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

ProgramC:OuterTableChosenbytheOptimizer

SQL 8.8

Program C

DECLARE CURSORJ CURSOR FOR
SELECT CNAME, CTYPE, INO, IEUR

FROM CUST, INVOICE

WHERE IEUR > :IEUR

AND
CCTRY = :CCTRY
AND
CUST.CNO = INVOICE.CNO

ORDER BY IEUR DESC

OPEN CURSORJ
FETCH CURSORJ while IEUR > :IEUR and CCTRY = :CCTRY

CLOSE CURSORJ

This is a neutral program. The optimizer estimates the filter factors for predi-
cates IEUR > :IEUR and CCTRY = :CCTRY, as we saw in Chapter 3, and then
chooses the table access order accordingly.
The predicate IEUR > :IEUR is not an easy one for the optimizer because
it does not know the range of values the user will enter for the field that is
associated with the host variable :IEUR. The optimizer may use a default value
like 33 or 50%. Then, if it chooses a nested-loop join, it is likely to choose the
same access path as for program A, namely the CUST outer table as shown in
Figure 8.6. This would result in a worst input LRT of 35 min instead of 10 min.

FF(CCTRY = :CCTRY)
= 10%

STAR T

100 KT

P,C

CNO CCTRY

2 MT

100 KT

P F,C

INO

CNO

CUST

1,000,000 rows

2 MT

INVOICE

20 invoices
per average customer

20,000,000 rows

Figure 8.6 Program C with current indexes.