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

To maximize the number of matching columns for any WHERE clause,

4 24 fat indexes would be needed in this case with the four search

columns, DOB, SEX, A, and B. By interviewing end users or by tracing their
queries, it may be possible to achieve more than one matching column for many
queries, by using a much smaller number of indexes. For example, if the users
are often interested in the sales per age group (FF = 10%) and sex, index (SEX,
DOB, A, B, CNO) would be better than any of the indexes shown in Figure 9.2
because men in their thirties, for instance, are next to each other. The QUBE for
scanning this particular index slice would be only 0.5 s because we would have
two matching columns and a filter factor of 0.05 instead of 0.1.

× 3 × 2 × 1 =

HUGE IMPACT OF THE TABLE ACCESS ORDER

The table access order in a star join is even more important than with a traditional
join. Suppose we started with the DATE dimension table, to identify the dates
for the week number required. We have about 3 years of entries in the table,
one row per day; as we are interested in only one week, the filter factor for the
DATE access is 7/1000 = 0 . 7%—only 7 rows will be accessed in the table. It
seems reasonable to assume that 0.7% of the SALES rows will be required also,
as shown in Figure 9.3. This results in 7,000,000 touches to the table SALES
and its index—or preferably just to the index.
The corresponding figures for starting with the STORE or ITEM table would
be even greater, so let's consider the possibility of accessing all the dimension
tables before reading the relevant rows in the fact table, SALES, as shown in
Figure 9.4. Of course, although each dimension table has a local predicate, there
are no join predicates between them.
If the fact table is the innermost table in the star join, the DBMS would
first have to build intermediate tables that would contain every valid foreign key
combination for the query as shown. These tables are Cartesian products of the

START

DATE STORE

1000 rows 0.7% 10,000 rows 10%

SALES

1,000,000,000 rows

1%

ITEM

Figure 9.3 Possible table
access order.

100,000 rows