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

CUST_BASIC

1 1

C C

CUST_PRIVATE CUST_COMPANY

1

C

CUST_DEATH

C = 0 or 1

Figure 8.23 Better than one table?

is either a company or a person, and few customers die twice! Combining the four
tables into one CUST table with several empty columns (in every row either the
company-related or the person-related columns are empty, as well as the death-
related columns for all customers that are still alive) is only a trade-off between
storage space and processing time (the number of random touches). Missing data
is not a violation of the normalization rules.
Consider a query that selects some basic columns and some company-related
columns for a given CNO. With one CUST table, this can be done with one
random touch, but with a multi-table solution the minimum will take two random
touches. The difference can be even greater; consider the familiar query shown
in SQL 8.18:

SQL 8.18

SELECT FNAME, CNO

FROM CUST

WHERE LNAME = :LNAME

AND
CITY = :CITY

ORDER BY FNAME

Index (CITY, LNAME) may not provide adequate performance, but we know
how to make this query very fast by simply adding columns to the index. The
two-table solution shown in Figure 8.24 requires a join, and it is a non-BJQ
join . We have seen what this leads to: either a lot of unproductive random index
touches to the inner table (or index) or MS/HJ with potentially high CPU time