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

executed by starting with the innermost SELECT. The result table is stored as a
temporary table, which is accessed by the next SELECT. Correlated subqueries
are normally executed by starting with the outermost SELECT. In any case,
as with joins, the indexes should be designed based on a table access order
that seems to produce the fastest access path. If the best table access order is
not chosen, the programmer may have to rewrite the statement, or—in some
cases—use a join.
DESIGNING INDEXES FOR UNIONS
The SELECT statements that are connected by UNION or UNION ALL are
optimized and executed one at a time. The indexes should therefore be designed
as appropriate for each individual SELECT. It should be noted that UNION with
an ORDER BY would cause early materialization.
TABLE DESIGN CONSIDERATIONS
We saw earlier that many database specialists believe that redundant data in tables
can never be appropriate. We also saw that the problems associated with the BJQ
need to be kept in mind when designing tables. We will now consider the table
design issues that affect SQL performance.
Redundant Data
There are two ways to make a join faster by using redundant data:
1. A column may be copied to the dependent table ( downward denormal-
ization ).
2. Summary data may be added to the parent table ( upward denormaliza-
tion ).
DownwardDenormalization
In our case study, we employed downward denormalization to eliminate the large
number of unproductive TRs to the inner table's (CUST) index; adding column
CCTRY to table INVOICE was a tempting solution, as reshown in Figure 8.21.
Additional storage cost is low and updating CCTRY in table INVOICE when
a customer moves to another country is not a major problem. If the INVOICE
rows of a customer are adjacent [index (CNO) is the clustering index], this
involves only one random table touch, together with several touches to move a
set of rows in index (CCTRY, IEUR, INO, CNO). Admittedly if a large customer
has 1000 invoices, these moves could cause 2000 random index touches taking
up to 20 s, but as this does not happen frequently, (large customers do not
often move to another country), this may be tolerable. In general, however, when