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

CNO,
DATENO,
ITEMNO,
STORENO,
EUR

DATENO,
ITEMNO,
STORENO,
CNO,
EUR

ITEMNO,
STORENO,
CNO,
DATENO,
EUR

STORENO,
CNO,
DATENO,
ITEMNO,
EUR

P,C

SNO

SALES

1,000,000,000 rows

Figure 9.8 Four fat indexes on the fact table—one per dimension.

on a fact table should have ample free space on the leaf pages, perhaps
30 or 40%.
Nevertheless, the greatest performance issue is the unpredictability of the
filter factors and the number of qualifying rows. If the query refers to an item
group that covers not 1% but 10% of the sales, the response time growth may
become unacceptable, as the final intermediate table would have 70 million rows.
If the number of qualifying fact table rows is 100 million instead of 10 million,
the enormous number of sequential touches makes the response time very long
even if the index rows were in a small number of slices.

SUMMARY TABLES

Even with ideal indexes, some queries to a fact table of one billion rows will result
in far too many touches. The only way to improve performance dramatically then
is by using summary tables ( query tables). These are denormalized fact tables.
They are feasible when the number of rows is not very high, perhaps only a few
million. Because of the denormalization, no joins are needed; all the required
columns are in one table.
If weekly sales queries are common, it naturally makes sense to build a
summary table for the sales by week. A good summary table for the example we
have discussed would have a row per sales by week, item, and store, as shown
in Figure 9.9. With three fat indexes, the typical response time would probably
be less than a minute. The summary table could be made significantly smaller
by storing sales by week and item group, as shown in Figure 9.10. In this case
the typical response time might be less than a second.