Nested loops revisited

Four variants of parallel index nested loop algorithms are compared with the parallel hybrid hash algorithm. The conclusions of experiments both with an analytic model and with an implementation in the Gamma parallel database system are that overall, parallel hybrid hash is the method of choice, but there are cases where nested loops with index wins big enough that systems could profit from implementing both algorithms. The experiments show further that among the nested loop algorithms, one of them, subset nested loops with sorting, clearly dominates.<<ETX>>

[1]  Jim Gray,et al.  The convoy phenomenon , 1979, OPSR.

[2]  Michael Stonebraker,et al.  Distributed query processing in a relational data base system , 1978, SIGMOD Conference.

[3]  David J. DeWitt,et al.  Practical Skew Handling in Parallel Joins , 1992, VLDB.

[4]  Patrick Valduriez,et al.  Join and Semijoin Algorithms for a Multiprocessor Database Machine , 1984, TODS.

[5]  Michael Stonebraker,et al.  The Case for Shared Nothing , 1985, HPTS.

[6]  Michael Stonebraker,et al.  Implementation techniques for main memory database systems , 1984, SIGMOD '84.

[7]  David J. DeWitt,et al.  Multiprocessor Hash-Based Join Algorithms , 1985, VLDB.

[8]  Jim Gray,et al.  A benchmark of NonStop SQL release 2 demonstrating near-linear speedup and scaleup on large databases , 1990, SIGMETRICS '90.

[9]  David J. DeWitt,et al.  A performance evaluation of four parallel join algorithms in a shared-nothing multiprocessor environment , 1989, SIGMOD '89.

[10]  Michael J. Carey,et al.  A performance evaluation of pointer-based joins , 1990, SIGMOD '90.

[11]  Honesty C. Young,et al.  A Symmetric Fragment and Replicate Algorithm for Distributed Joins , 1993, IEEE Trans. Parallel Distributed Syst..

[12]  Patrick Valduriez,et al.  Join indices , 1987, TODS.

[13]  Jim Gray,et al.  FastSort: a distributed single-input single-output external sort , 1990, SIGMOD '90.

[14]  M. W. Blasgen,et al.  Storage and Access in Relational Data Bases , 1977, IBM Syst. J..

[15]  Setrag Khoshafian,et al.  A decomposition storage model , 1985, SIGMOD Conference.

[16]  Philip S. Yu,et al.  An effective algorithm for parallelizing sort merge joins in the presence of data skew , 1990, [1990] Proceedings. Second International Symposium on Databases in Parallel and Distributed Systems.

[17]  Donovan A. Schneider,et al.  The Gamma Database Machine Project , 1990, IEEE Trans. Knowl. Data Eng..

[18]  Philip S. Yu,et al.  An effective algorithm for parallelizing sort merge joins in the presence of data skew , 1990, DPDS '90.

[19]  Philip S. Yu,et al.  An effective algorithm for parallelizing hash joins in the presence of data skew , 1991, [1991] Proceedings. Seventh International Conference on Data Engineering.

[20]  David J. DeWitt,et al.  Design and implementation of the wisconsin storage system , 1985, Softw. Pract. Exp..

[21]  Edward Omiecinski,et al.  Hash-Based and Index-Based Join Algorithms for Cube and Ring Connected Multicomputers , 1989, IEEE Trans. Knowl. Data Eng..