Databases Reference
In-Depth Information
The query path from a fact table begins with the fact table. A foreign key
column in the fact table joins to the Instance Key of a dimension table. In
Figure 11.4 the fact table foreign key joins to a Simple Instance Key in the
Item table. In Figure 11.5 the fact table foreign key joins to a Compound
Instance Key in the Item table. Then a d imension table continues the
query path by joining, via Instance Keys, to other dimension tables. In
Figure 11.4 the Item table joins to the Color_Formula and Carrier_Terms
tables via Simple Instance Keys. In Figure 11.5 the Item table joins to the
Color_Formula and Carrier_Terms tables via Compound Instance Keys.
The query path that begins with a fact table and then joins to dimension
tables usually is the result of a question that begins with the transactions
in the fact table. The query path can also begin with a dimension table.
A Department summary would begin with the Department dimension
table. A Region summary would begin with a Region dimension table. Or,
in the case of the example in Figure 11.4 and Figure 11.5, a Carrier_Terms
summary would begin with the Carrier_Terms table; a C olor_Formula
summary would begin with the Color_Formula table.
To optimize the join path from a dimension table (e.g., Carrier_Terms or
Color_Formula) backward toward the fact table, the foreign key Instance
Keys in all dimension tables will be indexed as a s econdary index. In
Figure 11.4 and Figure 11.5, the join path from the Color_Formula table
“backward” to the Item table will be optimized by a secondary index on
the Color_Formula Instance Key in the Item table; in addition, the join
path from the Carrier_Terms table “backward” to the Item table will be
optimized by a secondary index on the Carrier_Terms Instance Key in the
Item table. So, when the query path goes “backward” from a dimension
table, through other dimension tables, down to the fact table, the primary
keys and foreign keys reverse. The join from the Carrier_Terms table down
to the Item table finds the applicable rows in the Item table by finding the
Item rows wherein the Carrier_Terms Instance Key matches the Carrier_
Terms Instance Key of the Carrier_Terms table. The join from the Color_
Formula table down to the Item table finds the applicable rows in the Item
table by finding the Item rows wherein the Color_Formula Instance Key
matches the Color_Formula Instance Key of the Color_Formula table.
To optimize this join path, the Color_Formula Instance Key in the Item
table is covered by a s econdary index, and the Carrier_Terms Instance
Key in the Item table is also covered by a s econdary index. When the
Instance Keys are Simple Instance Keys, the covering secondary index is
an index of only the one Instance Key column. When the Instance Keys
