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

START

DATE

0.7%

STORE

1000 rows 10,000 rows 10%

SALES TEMP1

1,000,000,000
rows

7000 rows

TEMP2

1%

ITEM

Figure 9.4 Probably a better
table access order.

7,000,000 rows 100,000 rows

primary keys of those rows in the three dimension tables that satisfy their local
predicates. If the primary keys are short, the size of the intermediate tables in the
example might be less than 100 MB, probably small enough to fit in a database
buffer pool.
The number of rows created in the first temporary table will be

( 0 . 7% × 1000 ) × ( 10% × 10 , 000 ) = 7 × 1000 = 7000

The number of rows created in the second temporary table will be

7000 × ( 1% × 100 , 000 ) = 7000 × 1000 = 7 , 000 , 000

Figure 9.5 shows part of the second intermediate table that would be built,
the primary keys of the Cartesian product of the three dimension tables, DATE,
STORE, and ITEM. The intermediate table, as we have just calculated, would
contain 7,000,000 rows.

DATENO STORENO ITEMNO

...... ...... ........

764 235 1002

764 235 2890

764
764

235 4553

235 6179

...... ...... ........

Figure 9.5 Cartesian
product—intermediate table 2.

7,000,000 short rows