A new index for data warehouses

Authors

Abstract

There are several auxiliary pre-computed access structures that allow faster answers by reading less base data. Examples are materialized views, join indexes, B-tree and bitmap indexes. This paper proposes dimension-join, a new type of index especially suited for data warehouses. The dimension-join borrows ideas from several concepts. It is both a bitmap index and a multi-table join and when being used one of the tables is not read to improve performance. It is a multi-table join because it holds information belonging to two tables, which is similar to the join index proposed by Valduriez. However, instead of being composed by the tables primary keys, the dimension-join index is also a bitmap index over the fact table using values from a dimension column. The dimension-join index is very useful when selecting facts depending of dimension tables belonging to snowflakes. The dimension-join represents a direct connection between the fact table and a table in the snowflake that can avoid several joins and produce enormous performance improvements. This paper also evaluates experimentally the dimension-join indexes using the TPC-H benchmark and shows that this new index structure can dramatically improve the performance for some queries.