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

The most straightforward solution is to add a new column CLARGE to
table CUST, and add the appropriate predicate as shown in SQL 8.17L; when
CLARGE 1, the customer has at least one large invoice. Obviously, this would

be clumsy for two reasons:

=

1. Column CLARGE is not easy to maintain when INVOICE rows are
deleted or inserted.
2. Changing the definition of CLARGE would require a fairly massive
batch job.
Adding column CTOTAL IEUR, the total invoice value for each customer
to table CUST, together with the appropriate predicate as shown in SQL 8.17T
would eliminate these problems but would have higher maintenance overheads:
Every INSERT and DELETE INVOICE would require two random touches, one
to table CUST and one to an index that contains the CTOTAL IEUR column.
This solution would avoid some unproductive accesses to the index on table
INVOICE, those for customers whose total invoice value was not greater than
:IEUR; any customer whose total invoice value was greater than :IEUR, could
have one or more large invoices, or just several small ones.

SQL 8.17T

WHERE

CUST.CNO = INVOICE.CNO

AND CCTRY = :CCTRY

AND CTOTAL IEUR > :IEUR

AND IEUR > :IEUR

Adding column CMAX IEUR, the largest IEUR value of each customer's
invoices, (SQL 8.17M) to table CUST would have no unproductive accesses
to the index on table INVOICE. The maintenance problem would also be
slightly reduced because only those inserts and deletes for invoices that affected
CMAX IEUR would cause the latter to be updated. It should be noted, however,
that touches would still be needed on the CUST index to check the largest
IEUR value.

SQL 8.17M

WHERE

CUST.CNO = INVOICE.CNO

AND CCTRY = :CCTRY

AND CMAX IEUR > :IEUR

AND IEUR > :IEUR