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

ITEMNO,
STORENO,
DATEWEEK,
EUR

STORENO,
DATEWEEK,
ITEMNO,
EUR

DATEWEEK,
ITEMNO,
STORENO,
EUR

WEEKLY_SALES

One row per DATEWEEK,ITEMNO,STORENO
(when EUR > 0)

Figure 9.9 Summary table for Weekly Sales.

DATEWEEK,
ITEMGROUP,
STORENO,
EUR

ITEMGROUP,
STORENO,
DATEWEEK,
EUR

STORENO,
DATEWEEK,
ITEMGROUP,
EUR

WEEKLY_ITEMGROUP_SALES

One row per DATEWEEK,ITEMGROUP,STORENO
(when EUR > 0)

Figure 9.10 Summary table for Weekly Itemgroup Sales.

The indexes on summary tables tend to be quite small; the only limiting
factor is normally the time it takes to refresh the tables.
As with most things though, a new solution brings with it new problems:
1. If the users generate many different queries, designing summary tables
may be more difficult than designing the indexes . Fortunately, tools are
already available that propose summary tables based on traced queries.
2. Summary tables are of little value if the optimizer is not able to choose
the correct one; it is unrealistic to expect the end user to specify the