Enabling efficient access for queries on star schemas

Pair-wise join processing simplifies index design by using single-column indexes to join a fact table and the associated dimension tables according to AND predicates.

Procedure

Begin program-specific programming interface information.To design indexes to enable pair-wise join:

  1. Create an index for each key column in the fact able that corresponds to a dimension table.
  2. Partition by, and cluster data according to, commonly used dimension keys.
    Doing so can reduce the I/O that is required on the fact table for pair-wise join.

What to do next

If you require further performance improvement for some star schema queries, consider the following index design recommendations to encourage Db2 to use star join access:
  • Define a multi-column index on all key columns of the fact table. Key columns are fact table columns that have corresponding dimension tables.
  • If you do not have information about the way that your data is used, first try a multi-column index on the fact table that is based on the correlation of the data. Put less highly correlated columns later in the index key than more highly correlated columns.
  • As the correlation of columns in the fact table changes, reevaluate the index to determine if columns in the index should be reordered.
  • Define indexes on dimension tables to improve access to those tables.
  • When you have executed a number of queries and have more information about the way that the data is used, follow these recommendations:
    • Put more selective columns at the beginning of the multi-column index.
    • If a number of queries do not reference a dimension, put the column that corresponds to that dimension at the end of the index, or remove it completely.

      End program-specific programming interface information.