Abstract
Query generators producing sequences of SQL statements are embedded in many applications. As the response time of such sequences is often far from optimal, their optimization is an important issue. CGO (Coarse-Grained Optimization) is an appropriate optimization approach that applies rewrite rules to statement sequences. In previous work on CGO, a heuristic, priority-based control strategy was utilized to choose and execute rewrite rules. In this paper, we present an approach to enable cost-based optimization of statement sequences. We show how to exploit histogram propagation and the costing component of the underlying database system for this purpose. Our work extends previous work on histogram propagation. We conclude with experiments demonstrating the effectiveness of our approach.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
Kraft, T., Schwarz, H., Rantzau, R., Mitschang, B.: Coarse-Grained Optimization: Techniques for Rewriting SQL Statement Sequences. In: Proc. VLDB (2003)
Schwarz, H., Wagner, R., Mitschang, B.: Improving the Processing of Decision Support Queries: The Case for a DSS Optimizer. In: Proc. IDEAS (2001)
Kraft, T., Schwarz, H.: CHICAGO: A Test and Evaluation Environment for Coarse-Grained Optimization. In: Proc. VLDB (2004)
Kraft, T., Mitschang, B.: Statistics API: DBMS-independent Access and Management of DBMS Statistics in Heterogeneous Environments. In: Proc. ICEIS (2007)
Petkovic, M., Petkovic, L.: Complex Interval Arithmetic and Its Applications. Wiley-VCH, Chichester (1998)
Ioannidis, Y.: The History of Histograms (abridged). In: Proc. VLDB (2003)
Bruno, N., Chaudhuri, S., Gravano, L.: STHoles: A Multidimensional Workload-Aware Histogram. In: Proc. SIGMOD (2001)
Chaudhuri, S.: An Overview of Query Optimization in Relational Systems. In: Proc. PODS (1998)
Ioannidis, Y., Christodoulakis, S.: Optimal Histograms for Limiting Worst-Case Error Propagation in the Size of Join Results. ACM Transactions on Database Systems 18(4) (1993)
Ioannidis, Y., Poosala, V.: Histogram-Based Solutions to Diverse Database Estimation Problems. Data Engineering Bulletin 18(3) (1995)
Ioannidis, Y., Poosala, V.: Balancing Histogram Optimality and Practicality for Query Result Size Eestimation. In: Proc. SIGMOD (1995)
Poosala, V., Haas, P., Ioannidis, Y., Shekita, E.: Improved Histograms for Selectivity Estimation of Range Predicates. In: Proc. SIGMOD (1996)
Poosala, V., Ioannidis, Y.: Selectivity Estimation Without the Attribute Value Independence Assumption. In: Proc. VLDB (1997)
Ioannidis, Y., Poosala, V.: Histogram-Based Approximation of Set-Valued Query-Answers. In: Proc. VLDB (1999)
Poosala, V., Ganti, V., Ioannidis, Y.: Approximate Query Answering using Histograms. IEEE Data Engineering Bulletin 22(4) (1999)
Gibbons, P., Matias, Y., Poosala, V.: Fast Incremental Maintenance of Approximate Histograms. ACM Transactions on Database Systems 27(3) (2002)
Aboulnaga, A., Chaudhuri, S.: Self-tuning Histograms: Building Histograms Without Looking at Data. In: Proc. SIGMOD (1999)
IBM Corp.: IBM DB2 Universal Database, Administration Guide: Performance, Version 8.2
Oracle Corp.: Oracle Database Performance Tuning Guide, 10g Release 1 (10.1) (2003)
Hanson, E., Kollar, L.: Statistics Used by the Query Optimizer in Microsoft SQL Server 2005. Microsoft SQL Server TechCenter (1993)
Bruno, N., Chaudhuri, S.: Exploiting Statistics on Query Expressions for Optimization. In: Proc. SIGMOD (2002)
Garofalakis, M., Gibbons, P.: Approximate Query Processing: Taming the TeraBytes. In: Proc. VLDB (2001)
Grefen, P., de By, R.: A Multi-Set Extended Relational Algebra - A Formal Approach to a Practical Issue. In: Proc. ICDE (1994)
Garcia-Molina, H., Ullman, J., Widom, J.: Database Systems: The Complete Book. Prentice Hall PTR, Englewood Cliffs (2001)
TPC-H Standard Specification, Revision 2.0.0. (2002), www.tpc.org/tpch
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 2007 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Kraft, T., Schwarz, H., Mitschang, B. (2007). A Statistics Propagation Approach to Enable Cost-Based Optimization of Statement Sequences. In: Ioannidis, Y., Novikov, B., Rachev, B. (eds) Advances in Databases and Information Systems. ADBIS 2007. Lecture Notes in Computer Science, vol 4690. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-540-75185-4_20
Download citation
DOI: https://doi.org/10.1007/978-3-540-75185-4_20
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-75184-7
Online ISBN: 978-3-540-75185-4
eBook Packages: Computer ScienceComputer Science (R0)