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

INDEXES ON FACT TABLES

When the second intermediate table in Figure 9.4 has been created, the DBMS
must read all the fact rows that have the foreign key value combinations. Some-
times there will be several fact rows that contain the same combination of keys
(a store sells a particular item several times during a day); sometimes there will
be no fact rows relating to a row in the intermediate table. Consequently, it will
not be easy to estimate the number of touches to the fact table, but this could be
as high as one million.
A good fat index, such as the one shown in Figure 9.6, starting with the
most selective column, would be necessary to achieve an acceptable elapsed
time. Figure 9.7 makes it much easier to provide a quick estimate for the time
required by the fat index scan. It shows two possible ways in which the scan
could theoretically take place.
First, the optimizer might choose to scan a single slice of the index, where
the thickness of the scan was determined by the DATENO column alone; only
one matching column. The reason for this is that essentially we are searching
for a range of dates—the seven days in the week requested, DATENO values
764 to 770, for instance. We have already seen that this slice consists of 0 . 7% ×
1 , 000 , 000 , 000 = 7 , 000 , 000 rows.
Second, a “smart” optimizer could decide to subdivide this into what we
might call subscans. For each day (which has 1,000,000,000/1000

=

1 million
entries), we would have 1000 subslices based on the ITEMNO column (1% of
100,000 items). For the week, we would then have 7000 individual subslices
(two matching columns). The average thickness of each of these slices would be
1,000,000/100 10 rows. In this case, 7000 subslices each of thickness 10

rows would mean 70,000 rows in total.
A comparison of these two options would then be
1MC

, 000 =

1 × 10 ms + 7 , 000 , 000 × 0 . 01 ms = 70 s

2 MC 7000 × 10 ms + ( 7000 × 10 × 0 . 01 ) ms = 70 s + 0 . 7s = 71 s

DATENO,
ITEMNO,
STORENO,
CNO,
EUR

MERGE

TEMP2

7,000,000 rows

DATENO
STORENO
ITEMNO

SALES

Figure 9.6 Fat index on
the fact table.

1,000,000,000 rows