Indexes for efficient star schema processing
You can create indexes to enable Db2 to use special join methods for star schemas.
A
star schema is a database design that, in its simplest form, consists of a large table
called a fact table, and two or more smaller tables, called dimension
tables. More complex star schemas can be created by breaking one or more of the dimension
tables into multiple tables.
To access the data in a star schema design, you often write SELECT statements that include join operations between the fact table and the dimension tables, but no join operations between dimension tables. These types of queries are known as star-join queries.
For a star-join query, Db2 might use special join types, star join and pair-wise join, if the following conditions are true:
- The tables meet the conditions of a star join. (JOIN_TYPE='S')
- The tables meet the conditions of a pair-wise join. (JOIN_TYPE='P')
- The STARJOIN system parameter is set to ENABLE, and the number of tables in the query block is greater than or equal to the minimum number that is specified in the SJTABLES system parameter.
Whether Db2 uses star join, pair-wise join, or traditional join methods for processing a star schema query is based on which method results in the lowest cost access path. The existence of a star schema does not guarantee that either star join or pair-wise join access will be chosen.
