Join Index, Materialized View, and Hybrid-Hash Join: A Performance Analysis

This chapter deals with the problem of efficiently computing a join between two base relations in the presence of queries and updates to the base relations. We present a performance analysis of three methods: join index, materialized view, and hybrid-hash join. 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, in particular, the update activity on base relations, the join selectivity, and the amount of main memory available. A byproduct of this study is a strategy for incrementally maintaining a join index in the presence of updates to the underlying base relations.