An efficient hybrid join algorithm: a DB2 prototype

A new join method, called hybrid join, is proposed which uses the join-index filtering and the skip sequential prefetch mechanism for efficient data access. With this method, the outer table is sorted on the join column. Then, the outer is joined with the index on the join column of the inner. The inner tuple is represented by its surrogate, equivalent of its physical disk address, which is carried in the index. The partial join result is sorted on the surrogate and then the inner table is accessed sequentially to complete the join result. Local predicate filtering can also be applied before the access of the inner relation through the index AND/ORing. Efficient methods for skip sequential access and prefetching of logically discontiguous leaf pages of B⁺-tree indexes are also presented