Join index, materialized view, and hybrid-hash join: a performance analysis

The problem of efficiently computing a joint between two base relations in the presence of queries and updates to the base relations is dealt with. A performance analysis of three methods: join index, materialized view, and hybrid-hash join, is presented. The first two methods are examples of a strategy based on data caching; they represent two ends of a spectrum of possibilities depending on the attributes projected in the materialization. The third method is an example of a conventional strategy for computing a join from base relations. The results of this study show that the method of choice depends on the environment, and in particular on the update activity on base relations, the join selectivity, and the amount of main memory available. A by-product of this study is a strategy for incrementally maintaining a join index in the presence of updates to the underlying base relations