Star Join Considerations - Relational Database Index Design and the Optimizers - page 183

Chapter 9

Star Join Considerations

ž Introduction to star joins by means of an example
ž Role of Cartesian products, dimension tables, and fact tables
ž Comparison of these issues with ordinary joins
ž Index design for dimension and fact tables
ž Huge impact of the table access order with regard to the two types of
table and with Cartesian joins
ž Comparisons made using the QUBE
ž Limitations of star joins and the use of summary or query tables

INTRODUCTION

Star joins are different from traditional joins in two respects:

1. The central table in the starlike table structure such as the one shown in
Figure 9.1, called the fact table , is much larger than the ones surrounding
it, which are called dimension tables .
2. The best access path often contains Cartesian products of the dimension
tables; this means they do not have any common columns ; all combinations
of the dimension table rows will be joined, as long as they satisfy their
local predicates.

Kevin Viers (3, p. 611) puts this rather nicely when he talks of the Cartesian
join as “This might sound strange, but it makes sense.”
The fact table, SALES, in Figure 9.1 is huge, one billion rows, and con-
tains one row for every sale; which item was sold at which store on what date
and to whom ! The four dimension tables provide the detail of this information.
To understand what this means, let's take the example shown in SQL 9.1. We
have simplified the situation a little by using three dimension tables only—it is
assumed that no customer information is required.