This Article 
 Bibliographic References 
 Add to: 
Large Join Optimization on a Hypercube Multiprocessor
April 1994 (vol. 6 no. 2)
pp. 304-315

Optimizing large join queries that consist of many joins has been recognized as NP-hard. Most of the previous work focuses on a uniprocessor environment. In a multiprocessor, the location of each join adds another dimension to the complexity of the problem. In this paper, we examine the feasibility of exploiting the inherent parallelism in optimizing large join queries on a hypercube multiprocessor. This includes using the multiprocessor not only to answer the large join query but also to optimize it. We propose an algorithm to estimate the cost of a parallel large join plan. Three heuristics are provided for generating an initial solution, which is further optimized by an iterative local-improvement method. The entire process of parallel query optimization and execution is simulated on an Intel iPSC/2 hypercube machine. Our experimental results show that the performance of each heuristic depends on the characteristics of the query.

[1] D. Bittonet al., "Parallel algorithms for the execution of relational database operation,"ACM Trans. Database Syst., vol. 8, no. 3, pp. 324-353, Sept. 1983.
[2] P. Bodorik and J. S. Riordon, "Heuristic algorithms for distributed query processing," inProc. First Int. Conf. on Databases in Parallel and Distributed Systems, Austin, TX, Dec. 5-8, 1988, pp. 144-155.
[3] H. Boralet al., "Prototyping Bubba, a highly parallel database system,"IEEE Trans. Knowledge Data Eng., vol. 2, no. 1, pp. 4-24, Mar. 1990.
[4] S. M. Deen, D. N. P. Kannanagara, and M. C. Taylor, "Multi-join on parallel processors," inProc. 2nd Int. Symp. Databases in Parallel and Distributed Systems, pp. 92-102, 1990.
[5] D. Dewitt, R. H. Gerber, G. Graefe, M. L. Heytens, K. B. Kumar, and M. Muralikrishna, "GAMMA--A high performance dataflow database machine," inProc. 12th Int. Conf. VLDB, Kyoto, Japan, Aug. 1986, pp. 228-237.
[6] G. Graefe, "Encapsulation of parallelism in the Volcano query processing system," inProc. ACM SIGMOD Conf., Atlantic City, NJ, May 1990, p. 102.
[7] T. Ibaraki and T. Kameda, "On the optimal nesting order for computing N-relational joins,"ACM TODS, vol. 9, no. 3, pp. 482-502, Sept. 1984.
[8] Y. E. Ioannidis and Y. C. Kang, "Randomized algorithms for optimizing large join queries," inProc. ACM SIGMOD-Int. Conf. Management of Data, pp. 312-321, May 1990.
[9] M. Kitsuregawa, H. Tanaka, and T. Moto-Oka, "Architecture and performance of relational algebra machine GRACE," inInt. Conf. Parallel Processing Proc., pp. 241-250, Aug. 1984.
[10] R. Krishnamurthy, H. Boral, and C. Zaniolo, "Optimization of nonrecursive queries," inProc. 12th Int. Conf. Very Large Data Bases, Aug. 1986, pp. 128-137.
[11] E. T. Lin, "Join proceedings on a hypercube multicomputer," Ph.D. dissertation, Georgia Inst. of Technol., College of Computing, 1990.
[12] R. Lorie, J. Daudenarde, G. Hallmark, J. Stamos, and H. Young, "Adding intra-transaction parallelism to an existing DBMS: Early experience,"IEEE Data Eng. Bull., vol. 12, no. 1, Mar. 1989.
[13] E. Omiecinski and E. T. Lin, "Hash-based and index-based join algorithms for cube and ring connnected multicomputers,"IEEE Trans. Knowledge Data Eng., vol. 1, no. 3, pp. 329-343, Sept. 1989.
[14] D. A. Schneider and D. J. DeWitt, "Tradeoffs in processing complex join queries via hashing in multiprocessor database machines," inProc. Sixteenth Int. Conf. on Very Large Data Bases, Brisbane, Australia, Aug. 13-16, 1990, pp. 469-480.
[15] P. Selinger,et al., "Access path selection in a relational data base system," inProc. 1979 ACM-SIGMOD Int. Conf. Management of Data, Boston, MA, June 1979.
[16] L. D. Shapiro, "Join processing in database systems with large main memories,"ACM Trans. Database Syst., vol. 11, no. 3, pp. 239-264, Sept. 1986.
[17] M. Stonebraker, "The design of XPRS," inProc. 14th Int. Conf. VLDB, pp. 318-330, Los Angeles, Aug. 1988.
[18] A. Swami, "Optimizing large join queries: Combining heuristics and combinatorial techniques," inProc. ACM SIGMOD-Int. Conf. Management of Data, pp. 367-376, June 1989.
[19] A. Swami and A. Gupta, "Optimization of large join queries," inProc. 1988 ACM SIGMOD Int. Conf. Management of Data, June 1988, pp. 8-17.

Index Terms:
optimisation; computational complexity; simulated annealing; iterative methods; heuristic programming; relational algebra; hypercube networks; query processing; parallel programming; large join optimization; hypercube multiprocessor; large join queries; NP-hard problem; problem complexity; inherent parallelism; parallel large join plan; heuristics; initial solution; iterative local-improvement method; Intel iPSC/2 hypercube machine; performance; relational database
E.T. Lin, E.R. Omiecinski, S. Yalamanchili, "Large Join Optimization on a Hypercube Multiprocessor," IEEE Transactions on Knowledge and Data Engineering, vol. 6, no. 2, pp. 304-315, April 1994, doi:10.1109/69.277773
Usage of this product signifies your acceptance of the Terms of Use.