review-article

Robust Query Optimization Methods With Respect to Estimation Errors: A Survey

Authors:

Abdelkader Hameurlain,

Franck MorvanAuthors Info & Claims

ACM SIGMOD Record, Volume 44, Issue 3

Pages 25 - 36

https://doi.org/10.1145/2854006.2854012

Published: 03 December 2015 Publication History

Abstract

The quality of a query execution plan chosen by a Cost-Based Optimizer (CBO) depends greatly on the estimation accuracy of input parameter values. Many research results have been produced on improving the estimation accuracy, but they do not work for every situation. Therefore, "robust query optimization" was introduced, in an effort to minimize the sub-optimality risk by accepting the fact that estimates could be inaccurate. In this survey, we aim to provide an overview of robust query optimization methods by classifying them into different categories, explaining the essential ideas, listing their advantages and limitations, and comparing them with multiple criteria.

References

[1]

Aboulnaga, A. and Chaudhuri, S. 1999. Self-tuning Histograms: Building Histograms without Looking at Data. In SIGMOD. New York, USA, 181--192.

Digital Library

[2]

Amsaleg, L., al.1996. Scrambling Query Plans to Cope with Unexpected Delays. In PDIS. Miami, USA, 208--219.

Digital Library

[3]

Antonshenkov, G. 1993. Dynamic Query Optimization in Rdb/VMS. In ICDE. Vienna, Austria, 538--547.

Digital Library

[4]

Arcangeli, J.P., et al. 2004. Mobile Agent Based Self-Adaptive Join for Wide-Area Distributed Query Processing. Journal of Database Management, 15(4): 25--44.

[5]

Avnur, R. and Hellerstein, J.M. 2000. Eddies: Continuously Adaptive Query Processing. SIGMOD. Dallas, USA, 261--272.

Digital Library

[6]

Babu, S. and Bizarro, P. 2005. Adaptive Query Processing in the Looking Glass. In CIDR. Asilomar, USA, 238--249.

[7]

Babu, S., et al. 2005. Proactive Re-Optimization. In SIGMOD. Baltimore, USA, 107--118.

Digital Library

[8]

Babcock, B. and Chaudhuri, S. 2005. Towards a Robust Query Optimizer: A Principled and Practical Approach. In SIGMOD. Baltimore, USA, 119--130.

Digital Library

[9]

Bizarro, P., et al. 2005. Content-Based Routing: Different Plans for Different Data. In VLDB. Trondheim, Norway, 757--768.

Digital Library

[10]

Bizarro, P., et al. 2009. Progressive Parametric Query Optimization. KDE, 21(4): 582--594.

Digital Library

[11]

Bonneau, S. and Hameurlain, A. 1999. Hybrid Simultaneous Scheduling and Mapping in SQL Multi-Query Parallelization. In DEXA. Florence, Italy, 88--98.

Digital Library

[12]

Bouganim, L., et al. 2000. Dynamic Query Scheduling in Data Integration Systems. In ICDE. San Diego, USA, 425--434.

Digital Library

[13]

Bruno, N., et al. 2001. STHoles: a multidimensional workloadaware histogram. In SIGMOD. Santa Barbara, USA, 211--222.

Digital Library

[14]

Bruno, N., et al. 2011. AutoAdmin Project at Microsoft Research: Lessons Learned. IEEE Data Eng. Bull, 34(4): 12--19.

[15]

Bruno, N., et al. 2013. Continuous Cloud-Scale Query Optimization and Processing. PVLDB, 6(11): 961--972.

Digital Library

[16]

Cao, L. and Rundensteiner, E.A. 2013. High Performance Stream Query Processing With Correlation-Aware Partitioning. PVLDB, 7(4): 265--276.

Digital Library

[17]

Chaudhuri, S., et al. 2008. A Pay-As-You-Go Framework for Query Execution Feedback. PVLDB, 1(1): 1141--1152.

Digital Library

[18]

Chaudhuri, S. 2009. Query Optimizers: Time to Rethink the Contract? In SIGMOD. Providence, USA, 961--968.

Digital Library

[19]

Chaudhuri, S., et al. 2009. Exact Cardinality Query Optimization for Optimizer Testing. PVLDB, 2(1): 994--1005.

Digital Library

[20]

Chen, C.M. and Roussopoulos, N. 1994. Adaptive Selectivity Estimation Using Query Feedback. In SIGMOD. Minneapolis, USA, 161--172.

Digital Library

[21]

Chu, F., Halpern, J., Gehrke, J. 2002. Least expected cost query optimization: what can we expect? In PODS. Madison, USA, 293--302.

Digital Library

[22]

Cole, R.L. and Graefe, G. 1994. Optimization of Dynamic Query Evaluation Plans. In SIGMOD. Minneapolis, 150--160.

Digital Library

[23]

Deshpande, A. 2004. An Initial Study of Overheads of Eddies. SIGMOD Record, 33(1): 44--49.

Digital Library

[24]

Deshpande, A., et al. 2007. Adaptive Query Processing. Foundations and Trends in Databases, 1(1): 1--140.

Digital Library

[25]

Dutt, A. and Haritsa, J. 2014. Plan bouquets: query processing without selectivity estimation. In SIGMOD. Snowbird, USA, 1039--1050.

Digital Library

[26]

Ergenç, B., et al. 2007. Robust Placement of Mobile Relational Operators for Large Scale Distributed Query Optimization. In PDCAT. Adelaide, Australia, 227--235.

Digital Library

[27]

Evrendilek, C., et al. 1997. Multidatabase Query Optimization. Distributed and Parallel Databases, 5(1):77--114.

Digital Library

[28]

Ghazal, A., et al. 2012. Adaptive Optimizations of Recursive Queries in Teradata. In SIGMOD. Scottsdale, USA, 851--860.

Digital Library

[29]

Graefe, G. and Ward, K. 1989. Dynamic query evaluation plans. In SIGMOD. Portland, USA, 358--366.

Digital Library

[30]

Graefe, G., et al. 2009. Visualizing the Robustness of Query Execution. In CIDR. Asilomar, USA.

[31]

Graefe, G., et al. 2010. Robust Query Processing. Dagstuhl Workshop Summary 10381, Wadern, Germany.

[32]

Graefe, G. 2011. Robust Query Processing (Research Panel). In ICDE. Hannover, Germany, 1361.

Digital Library

[33]

Graefe, G., et al. 2012. Robust Query Processing. Dagstuhl Workshop Summary 12321, Wadern, Germany.

[34]

Gounaris, A., et al. 2002. Adaptive Query Processing: A Survey. In BNCOD. Sheffield, UK, 11--25.

Digital Library

[35]

Gounaris, A., et al. 2013. Adaptive Query Processing in Distributed Settings. Advanced Query Processing, Vol. 1: 211--236.

[36]

Hameurlain, A. and Morvan, F. 2002. CPU and Incremental Memory Allocation in Dynamic Parallelization of SQL Queries. Journal of Parallel Computing, 28(4): 525--556.

Digital Library

[37]

Han, W., et al. 2007. Progressive Optimization in a Shared-Nothing Parallel Database. In SIGMOD. Beijing, 809--820.

Digital Library

[38]

Harish, D., et al. 2007. On the Production of Anorexic Plan Diagrams. In VLDB. Vienna, Austria, 1081--1092.

Digital Library

[39]

Harish, D., et al. 2008. Identifying Robust Plans through Plan Diagram Reduction. PVLDB, 1(1): 1124--1140.

Digital Library

[40]

Herodotou, H. and Babu, S. Xplus. 2010. A SQL-Tuning-Aware Query Optimizer. PVLDB, 3(1): 1149--1160.

Digital Library

[41]

Hong, W. and Stonebraker, M. 1993. Optimization of Parallel Query Execution Plans in XPRS. Distributed and Parallel Databases, 1(1): 9--32.

Digital Library

[42]

Ioannidis, Y. and Christodoulakis, S. 1991. On the Propagation of Errors in the Size of Join Results. In SIGMOD. Denver, USA, 168--177.

Digital Library

[43]

Ioannidis, Y. 2003. The History of Histograms (abridged). In VLDB. Berlin, Germany, 19--30.

Digital Library

[44]

Ives, Z. G., et al. 1999. An Adaptive Query Execution System for Data Integration. In SIGMOD. Philadelphia, USA, 299--310.

Digital Library

[45]

Ives, Z.G., et al. 2004. Adapting to Source Properties in Processing Data Integration Queries. In SIGMOD. Paris, France, 395--406.

Digital Library

[46]

Kabra, N. and DeWitt, D. 1998. Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans. In SIGMOD. Seattle, USA, 106--117.

Digital Library

[47]

Larson, P., et al. 2007. Cardinality Estimation Using Sample Views with Quality Assurance. In SIGMOD. Beijing, 175--186.

Digital Library

[48]

Lipton, R.J., et al. 1990. Practical Selectivity Estimation through Adaptive Sampling. In SIGMOD. Atlantic City, 1--11.

Digital Library

[49]

Mannino, M., et al. 1988. Statistical profile estimation in database systems. ACM Computing Surveys, 20(3): 191--221.

Digital Library

[50]

Markl, V., et al. 2004. Robust Query Processing through Progressive Optimization. SIGMOD. Paris, France, 659--670.

Digital Library

[51]

Markl, V., et al. 2007. Consistent Selectivity Estimation via Maximum Entropy. VLDB Journal, 16(1): 55--76.

Digital Library

[52]

Morvan, F. and Hameurlain, A. 2009. Dynamic Query Optimization: Towards Decentralized Methods. International Journal of Intelligent Information and Database Systems, 4(3): 461--482.

Digital Library

[53]

Neumann, T. and Calindo-Legaria, C. 2013.Taking the Edge off Cardinality Estimation Errors using Incremental Execution. In BTW. Magdeburg, Germany, 73--92.

[54]

Nehme, R.V., et al. 2009. Query Mesh: Multi-Route Query Processing Technology (Demo). PVLDB, 2(2): 1530--1533.

Digital Library

[55]

Nehme, R.V., et al. 2013. Multi-Route Query Processing and Optimization. Journal of Computer and System Sciences, 79(3): 312--329.

Digital Library

[56]

Olken, F. and Rotem, D. 1986. Simple Random Sampling from Relational Databases. In VLDB. Kyoto, Japan, 160--169.

Digital Library

[57]

Picasso Database Query Optimizer Visualizer. http://dsl.serc.iisc.ernet.in/projects/PICASSO/

[58]

Polyzotis, N. 2005. Selectivity-based partitioning: a divideand-union paradigm for effective query optimization. In CIKM. Bremen, Germany, 720--727.

Digital Library

[59]

Raman, V., et al. 2003. Using State Modules for Adaptive Query Processing. In ICDE. Bangalore, India, 353--364.

[60]

Reddy, N. and Harista, J. 2005. Analyzing Plan Diagrams of Database Query Optimizers. In VLDB. Trondheim, Norway, 1228--1239.

Digital Library

[61]

Selinger, P.G., et al. 1979. Access Path Selection in a Relational DBMS. In SIGMOD. Boston, USA, 23--34.

Digital Library

[62]

Srivastava, Uet al. 2006. ISOMER: Consistent Histogram Construction Using Query Feedback. In ICDE. Atlanta, 39.

Digital Library

[63]

Stillger, M., et al.2001. LEO-DB2's Learning Optimizer. VLDB. Roma, Italy, 19--28.

Digital Library

[64]

Tian, F. and DeWitt, D.J. 2003. Tuple Routing Strategies for Distributed Eddies. In VLDB. Berlin, Germany, 333--344.

Digital Library

[65]

Tzoumas, K., et al. 2010. Sharing-Aware Horizontal Partitioning for Exploiting Correlations during Query Processing. PVLDB, 3(1): 542--553.

Digital Library

[66]

Tzoumas, K., et al. 2011. Lightweight Graphical Models for Selectivity Estimation without Independence Assumptions. PVLDB, 4(11): 852--863.

Digital Library

[67]

Tzoumas, K., et al. 2013. Efficient Adapting Graphical Models for Selectivity Estimation. VLDB Journal, 22(1): 3--27.

Digital Library

[68]

Wilschut, A. N. and Apers, P. M. G. 1991. Dataflow Query Execution in a Parallel Main-Memory Environment. In PDIS, Miami Beach, USA, 68--77.

Digital Library

[69]

Wiener, J.L., et al. 2009. Benchmarking Query Execution Robustness. In TPC Technology Conference on Performance Evaluation & Benchmarking. Lyon, France, 153--166.

Digital Library

[70]

Zhou, Y., et al. 2005. An Adaptable Distributed Query Processing Architecture. Knowledge and Data Engineering. 53(3): 283--309.

Digital Library

Cited By

Liu XHuang YLin XKumar AAlsudais SLi C(2024)Pasta: A Cost-Based Optimizer for Generating Pipelining Schedules for Dataflow DAGsProceedings of the ACM on Management of Data10.1145/36988322:6(1-26)Online publication date: 20-Dec-2024
https://dl.acm.org/doi/10.1145/3698832
Kamali AKantere VZuzarte C(2024)Robust Query Optimization in the Era of Machine Learning: State-of-the-Art and Future Directions2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00408(5371-5375)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00408
Liang FLau FCui HLi YLin BLi CHu X(2024)RelJoin: Relative-cost-based selection of distributed join methods for query plan optimizationInformation Sciences10.1016/j.ins.2023.120022658(120022)Online publication date: Feb-2024
https://doi.org/10.1016/j.ins.2023.120022
Show More Cited By

Robust Query Optimization Methods With Respect to Estimation Errors: A Survey
1. Information systems
  1. Data management systems
    1. Database management system engines
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

Robust query processing through progressive optimization
SIGMOD '04: Proceedings of the 2004 ACM SIGMOD international conference on Management of data

Virtually every commercial query optimizer chooses the best plan for a query using a cost model that relies heavily on accurate cardinality estimation. Cardinality estimation errors can occur due to the use of inaccurate statistics, invalid assumptions ...
Robust variance estimation for random effects meta-analysis

In random effects meta-analysis, an overall effect is estimated using a weighted mean, with weights based on estimated marginal variances. The variance of the overall effect is often estimated using the inverse of the sum of the estimated weights, and ...
Handling estimation errors in database query processing

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record

ACM SIGMOD Record Volume 44, Issue 3

September 2015

57 pages

ISSN:0163-5808

DOI:10.1145/2854006

Editors:
Yanlei Diao
University of Massachusetts Amherst
,
Pablo Barceló
Universidad de Chile
,
Vanessa Braganholo
Universidade Federal Fluminense
,
Marco Brambilla
Politecnico di Milano
,
Chee Yong Chan
National University of Singapore
,
Rada Chirkova
North Carolina State University
,
Anastasios Kementsietsidis
Google Research
,
Olga Papaemmanoui
Brandeis Univesity
,
Aditya Parameswaran
University of Illinois
,
Anish Das Sarma
Google Research
,
Alkis Simitsis
HP Labs
,
Nesime Tatbul
ETH Zurich
,
Marianne Winslett
University of Illinois
,
Jun Yang
Duke University

Issue’s Table of Contents

Copyright © 2015 Authors.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 03 December 2015

Published in SIGMOD Volume 44, Issue 3

Check for updates

Qualifiers

Review-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

18
Total Citations
View Citations
443
Total Downloads

Downloads (Last 12 months)37
Downloads (Last 6 weeks)9

Reflects downloads up to 25 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Liu XHuang YLin XKumar AAlsudais SLi C(2024)Pasta: A Cost-Based Optimizer for Generating Pipelining Schedules for Dataflow DAGsProceedings of the ACM on Management of Data10.1145/36988322:6(1-26)Online publication date: 20-Dec-2024
https://dl.acm.org/doi/10.1145/3698832
Kamali AKantere VZuzarte C(2024)Robust Query Optimization in the Era of Machine Learning: State-of-the-Art and Future Directions2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00408(5371-5375)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00408
Liang FLau FCui HLi YLin BLi CHu X(2024)RelJoin: Relative-cost-based selection of distributed join methods for query plan optimizationInformation Sciences10.1016/j.ins.2023.120022658(120022)Online publication date: Feb-2024
https://doi.org/10.1016/j.ins.2023.120022
Song HZhou WLi FPeng XCui H(2023)Rethink Query Optimization in HTAP DatabasesProceedings of the ACM on Management of Data10.1145/36267501:4(1-27)Online publication date: 12-Dec-2023
https://dl.acm.org/doi/10.1145/3626750
Wang ZZeng KHuang BChen WCui XWang BLiu JFan LQu DHou ZGuan TLi CZhou J(2023)Tempura: a general cost-based optimizer framework for incremental data processing (Journal Version)The VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-023-00785-132:6(1315-1342)Online publication date: 20-Mar-2023
https://dl.acm.org/doi/10.1007/s00778-023-00785-1
Heling LAcosta M(2022)Robust query processing for linked data fragmentsSemantic Web10.3233/SW-21288813:4(623-657)Online publication date: 31-May-2022
https://doi.org/10.3233/SW-212888
B. S. Praciano FAmora PAbreu IPereira FMachado J(2021)Robust Cardinality: a novel approach for cardinality prediction in SQL queriesJournal of the Brazilian Computer Society10.1186/s13173-021-00115-927:1Online publication date: 1-Sep-2021
https://doi.org/10.1186/s13173-021-00115-9
Wang ZZeng KHuang BChen WCui XWang BLiu JFan LQu DHou ZGuan TLi CZhou J(2020)TempuraProceedings of the VLDB Endowment10.14778/3421424.342142714:1(14-27)Online publication date: 1-Sep-2020
https://dl.acm.org/doi/10.14778/3421424.3421427
Heling LAcosta M(2020)Cost- and Robustness-Based Query Optimization for Linked Data FragmentsThe Semantic Web – ISWC 202010.1007/978-3-030-62419-4_14(238-257)Online publication date: 2-Nov-2020
https://dl.acm.org/doi/10.1007/978-3-030-62419-4_14
Azhir EJafari Navimipour NHosseinzadeh MSharifi ADarwesh A(2019)Deterministic and non‐deterministic query optimization techniques in the cloud computingConcurrency and Computation: Practice and Experience10.1002/cpe.524031:17Online publication date: 5-Mar-2019
https://doi.org/10.1002/cpe.5240
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents