Optimizing Large Query by Simulated Annealing Algorithm Based On Graph-Based Approach

In general the ordering of join-operations is quite sensitive and has a devastatingly negative effect on the efficiency of the DBMS.  Scheufele and Moerkotte proved that join-ordering is NP-complete in the general case [1]. The dynamic programming algorithm has a worst case running time, thus for queries with more than 10 joins, it becomes infeasible. To resolve this problem, random strategies are used.  Simulated annealing is an intelligent algorithm of developing very fast. In this paper, we introduce the traditional simulated annealing algorithm through discussing its theory and process, analyzes its shortcoming in detail, simple describe influence of key parameters to simulated annealing algorithm and provided feasible improvement. Especially, in order to avoid the deficiency resulted by the neighbors of state, and make the query optimization support complex non-inner join, the improved algorithm gives a semantics expression of query and a method of constructing the connected join pairs. Experimental results show that the improved algorithm outperforms the original algorithm in terms of both output quality and running time.

[1]  Arun N. Swami,et al.  Optimization of large join queries: combining heuristics and combinatorial techniques , 1989, SIGMOD '89.

[2]  Yannis E. Ioannidis,et al.  Randomized algorithms for optimizing large join queries , 1990, SIGMOD '90.

[3]  Guido Moerkotte,et al.  On the complexity of generating optimal plans with cross products (extended abstract) , 1997, PODS '97.

[4]  Allen Van Gelder,et al.  Measuring the effectiveness of optimization. Search Strategies , 1992, BDA.

[5]  Arun N. Swami,et al.  Optimization of large join queries , 1988, SIGMOD '88.

[6]  Eugene Wong,et al.  Query optimization by simulated annealing , 1987, SIGMOD '87.

[7]  Hamid Pirahesh,et al.  Using EELs, a practical approach to outerjoin and antijoin reordering , 2001, Proceedings 17th International Conference on Data Engineering.

[8]  Arnon Rosenthal,et al.  Outerjoin simplification and reordering for query optimization , 1997, TODS.

[9]  Guido Moerkotte,et al.  Heuristic and randomized optimization for the join ordering problem , 1997, The VLDB Journal.

[10]  Joseph M. Hellerstein,et al.  Eddies: continuously adaptive query processing , 2000, SIGMOD '00.

[11]  Hongjun Lu,et al.  A note on the strategy space of multiway join query optimization problem in parallel systems , 1991, SGMD.

[12]  Stavros Christodoulakis,et al.  On the propagation of errors in the size of join results , 1991, SIGMOD '91.

[13]  Patrick Valduriez,et al.  On the Effectiveness of Optimization Search Strategies for Parallel Execution Spaces , 1993, VLDB.