Issue No.04 - April (2009 vol.21)
pp: 582-594
Pedro Bizarro , University of Coimbra, Coimbra
Nicolas Bruno , Microsoft Corp., Redmond
David J. DeWitt , University of Wisconsin, Madison
Commercial applications usually rely on pre-compiled parameterized procedures to interact with a database. Unfortunately, executing a procedure with a set of parameters different from those used at compilation time may be arbitrarily sub-optimal. Parametric query optimization (PQO) attempts to solve this problem by exhaustively determining the optimal plans at each point of the parameter space at compile time. However, PQO is likely not cost-effective if the query is executed infrequently or if it is executed with values only within a subset of the parameter space. In this paper we propose instead to progressively explore the parameter space and build a parametric plan during several executions of the same query. We introduce algorithms that, as parametric plans are populated, are able to frequently bypass the optimizer but still execute optimal or near-optimal plans.
Query processing, Database Management
Pedro Bizarro, Nicolas Bruno, David J. DeWitt, "Progressive Parametric Query Optimization", IEEE Transactions on Knowledge & Data Engineering, vol.21, no. 4, pp. 582-594, April 2009, doi:10.1109/TKDE.2008.160
[1] S. Babu and P. Bizarro, “Adaptive Query Processing in the Looking Glass,” Proc. Second Biennial Conf. Innovative Data Systems Research (CIDR), 2005.
[2] R.L. Cole and G. Graefe, “Optimization of Dynamic Query Evaluation Plans,” Proc. ACM SIGMOD, 1994.
[3] D. Harish, P. Darera, and J. Haritsa, “On the Production of Anorexic Plan Diagrams,” Proc. 33rd Int'l Conf. Very Large Data Bases (VLDB), 2007.
[4] A. Deshpande, Z. Ives, and V. Raman, “Adaptive Query Processing,” Foundations and Trends in Databases, vol. 1, no. 1, pp. 1-140, 2007.
[5] S. Ganguly, “Design and Analysis of Parametric Query Optimization Algorithms,” Proc. 24th Int'l Conf. Very Large Data Bases (VLDB), 1998.
[6] A. Ghosh, J. Parikh, V.S. Sengar, and J.R. Haritsa, “Plan Selection Based on Query Clustering,” Proc. 28th Int'l Conf. Very Large Data Bases (VLDB), 2002.
[7] G. Graefe and K. Ward, “Dynamic Query Evaluation Plans,” Proc. ACM SIGMOD, 1989.
[8] A. Hulgeri and S. Sudarshan, “Parametric Query Optimization for Linear and Piecewise Linear Cost Functions,” Proc. 28th Int'l Conf. Very Large Data Bases (VLDB), 2002.
[9] A. Hulgeri and S. Sudarshan, “AniPQO: Almost Non-Intrusive Parametric Query Optimization for Nonlinear Cost Functions,” Proc. 28th Int'l Conf. Very Large Data Bases (VLDB), 2003.
[10] Y.E. Ioannidis, R.T. Ng, K. Shim, and T.K. Sellis, “Parametric Query Optimization,” Proc. 18th Int'l Conf. Very Large Data Bases (VLDB), 1992.
[11] N. Kabra and D.J. DeWitt, “Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans,” Proc. ACM SIGMOD, 1998.
[12] G.M. Lohman, “Is Query Optimization a “Solved” Problem?” Proc. Workshop Database Query Optimization, Oregon Graduate Center Technical Report 89-005, 1989.
[13] Microsoft Corp., “Plan Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint,” SQL Server 2005 Books Online, 2005.
[14] V.G.V. Prasad, “Parametric Query Optimization: A Geometric Approach,” MSc thesis, IIT, Kampur, 1999.
[15] S.V.U. Maheswara Rao, “Parametric Query Optimization: A Non-Geometric Approach,” master's thesis, IIT, Kampur, 1999.
[16] N. Reddy and J.R. Haritsa, “Analyzing Plan Diagrams of Database Query Optimizers,” Proc. 31st Int'l Conf. Very Large Data Bases (VLDB), 2005.
[17] Transaction Processing Performance Council, The TPC-H Benchmark, http:/, accessed, Mar. 2006.