Chapter 8. Indexing for Table Joins

Summary

An introduction to, and the terms used by, the most common join techniques. Simple table join examples to illustrate the processing that takes place in a nested loop join - the most common join method; a comparison of a two table join SELECT with a program implemented two table access. The terms involved in the join process together with their significance: local and join predicates; the table access order; inner and outer tables. A case study to consider the importance of the table access order on the index design process, using the QUBE to illustrate the relative performance of three different program implementations. Merge scan joins and hash joins; comparisons with nested loop joins; why these join techniques have become more widely used with current hardware. Adapting the Basic Question, BQ, to table joins to formulate the basic join question, BJQ. Index design considerations when joining more than two tables together, and when using subqueries and unions. Why joins often perform poorly. Table design issues that should be considered with respect to joins; downward and upward denormalization; the cost of denormalization; NLJ and MS/HJ compared to denormalization; unconscious table design.