Database Reference
In-Depth Information
In a star join, the fact table would normally have a huge number of local
rows. In this case, at least according to the rule of thumb, the fact table should
be the
innermost
table in a nested loop join. This implies Cartesian joins as we
will see in due course because the dimension tables typically do not have any
common columns.
As we did in Chapter 8, we will now consider the two major issues affecting
the performance of joins; the
indexes on the tables
and the
table access order
.
As we do this, the concept of the Cartesian join should become clear. We will
first discuss the indexes on the dimension tables, then the table access order, and
finally the indexes on the fact table.
INDEXES ON DIMENSION TABLES
If the end users are able to generate predicates with
no restrictions
, the indexes
on the dimension tables need to be adequate for
any
local predicate combination.
Figure 9.2 shows just one of our four dimension tables, the Customer table. The
first version could be one fat index per search column. With sufficient statistics,
including histograms, the optimizer is likely to choose the most selective index,
but in most cases there will only be
one
matching column.
The number of sequential touches to the index will be the product of the
filter factors (FF) for the
matching
predicates multiplied by the number of rows
in the dimension table, for example:
WHERE
DOB BETWEEN :BDATE1 AND :BDATE2
FF = 10%
AND
SEX = :SEX FF = 50%
These local predicates on the dimension table CUST would result
in
) in Figure 9.2
because there is only
one
matching predicate; this is because the range predicate
column, DOB, is the
last
matching column. According to the QUBE, this step
would take one second.
0
.
1
×
1
,
000
,
000
=
100
,
000 sequential touches to index (DOB,
...
U
U
U
U
DOB,
SEX,
A,
B,
CNO
SEX,
A,
B,
DOB,
CNO
A,
B,
DOB,
SEX,
CNO
B,
DOB,
SEX,
A,
CNO
P,C
CNO
CUST (dimension table)
Figure 9.2
Starting point:
at least one matching column.
1,000,000 rows
Search WWH ::
Custom Search