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

High TS per FETCH Low TR per FETCH

F,C

STAR T

CCTRY,
CNAME,
CNO,
CHWM_IEUR,...

CNO,
IEUR DESC ,
INO

P,C

P

INO

CNO

CUST + CHWM_IEUR INVOICE

1,000,000 rows

20,000,000 rows

Figure 8.22 Ideal indexes that almost satisfy the basic join question.

The best solution (refer to Fig. 8.22 and the SQL statement shown in SQL
8.17H) may well be to add CHWM IEUR, a high-water mark, the largest IEUR
value that a customer has recently had but not necessarily still has .Herewe
are trying to balance the cost of the maintenance against the benefits achieved.
CHWM IEUR does not have to be exact, but it must be high enough to catch
all large invoices; if it is too high, the invoice table's index will simply be
checked unnecessarily. Constantly updating CHWM IEUR will incur excessive
costs, and so the high-water marks need only be periodically refreshed, as far
as deletes are concerned, by means of a CHWM IEUR update refresh program;
these deletes will cause CHWM IEUR to be reduced, thereby eliminating the
unproductive TRs. As far as inserts are concerned, CHWM IEUR needs to
be updated only when a new invoice to a customer has a higher IEUR value
than any existing invoice for that customer; this only needs one random touch
to the index on table CUST for every INSERT INVOICE to check the value
of CHWM IEUR.

SQL 8.17H

WHERE
CUST.CNO = INVOICE.CNO

AND CCTRY = :CCTRY

AND CHWM IEUR > :IEUR

AND IEUR > :IEUR