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

Chapter 8

Indexing for Table Joins

ž 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
ž Comparison of a two-table join SELECT with a program-implemented
two-table access
ž Terms involved in the join process together with their significance
ž Local and join predicates
ž Table access order
ž Inner and outer tables
ž 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 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
ž Cost of denormalization
ž NLJ and MS/HJ compared to denormalization
ž Unconscious table design

INTRODUCTION

All the examples we have encountered so far have been single-table SELECTs.
It is now time to consider SELECT statements that access several tables, table