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

DATE
1000 rows

STORE

10,000 rows

1,000,000,000
rows

SALES

CUST ITEM

Figure 9.1 Star schema—a large fact
table with small dimension tables.

1,000,000 rows 100,000 rows

SQL 9.1

SELECT SUM (EUR)

FROM SALES, DATE, ITEM, STORE

WHERE ITEM.GROUP = 901

AND
DATE.WEEK = 327
AND
STORE.REGION = 101
AND
SALES.ITEMPK = ITEM.ITEMPK
AND
SALES.DATEPK = DATE.DATEPK
AND
SALES.STOREPK = STORE.STOREPK

This star join is extremely simple; it determines the total sales (EUR) of
one product group during one week in one geographical area . The product group
code is in table ITEM, the week number in table DATE, and the area code in
table STORE.
The star schema (and the star join) has become common in data warehouses
because it is easy to generate a single SELECT statement (a join), which produces
the result table while the end users see the data as an n -dimensional cube.
The border between traditional joins and star joins is drawn in water. The
four-table join shown in Figure 8.26 may look like a star join, especially if the
code tables are drawn around the customer table. However, the SELECT state-
ment is not a star join because the second characteristic does not apply; Cartesian
joins of the code tables would make no sense at all. The local-row rule of thumb
considered in Chapter 8 and, indeed, common sense tell us that the outermost
table should be CUST. The order in which the other tables are accessed does not
make any difference.