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

STAR T

20 T Roughly 2 touches

per result row

20 T

U CCTRY,

CNO,
CNAME,
CTYPE

U

CCTRY,
IEUR DESC ,
INO,
CNO

P,C F,C

P

CNO CNO

INO

CUST INVOICE (+CCTRY)

1,000,000 rows 20,000,000 rows

Figure 8.21 Ideal indexes for a nested-loop join that satisfies the basic join question.

downward denormalization is considered, it is important to predict the number
of worst-case random index touches that take place when the redundant column
(CCTRY in our example) is updated.

UpwardDenormalization
Let us assume that the sort requirement in our case study is ORDER BY CNAME,
CNO instead of ORDER BY IEUR DESC. Now, a nested loop with CUST as
the outer table would eliminate a sort. This access path would then have the
potential for making a transaction with 20 FETCHes (one screen) very fast.
Without redundant data, however, it could take 1000 random touches—10 s
according to the QUBE—to an index on table INVOICE to find one result row,
assuming that only 0.1% of the one million customers have at least one large
invoice. Upward denormalization should then be considered to reduce the number
of these nonproductive random touches, which are required to determine whether
a customer has any large invoices.

SQL 8.17L

WHERE
CUST.CNO = INVOICE.CNO

AND CCTRY = :CCTRY

AND CLARGE = 1

AND IEUR > :IEUR