A Statistics Propagation Approach to Enable Cost-Based Optimization of Statement Sequences

  • Tobias Kraft
  • Holger Schwarz
  • Bernhard Mitschang
Part of the Lecture Notes in Computer Science book series (LNCS, volume 4690)


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.


cost-based query optimization query processing histograms 


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Kraft, T., Schwarz, H., Rantzau, R., Mitschang, B.: Coarse-Grained Optimization: Techniques for Rewriting SQL Statement Sequences. In: Proc. VLDB (2003)Google Scholar
  2. 2.
    Schwarz, H., Wagner, R., Mitschang, B.: Improving the Processing of Decision Support Queries: The Case for a DSS Optimizer. In: Proc. IDEAS (2001)Google Scholar
  3. 3.
    Kraft, T., Schwarz, H.: CHICAGO: A Test and Evaluation Environment for Coarse-Grained Optimization. In: Proc. VLDB (2004)Google Scholar
  4. 4.
    Kraft, T., Mitschang, B.: Statistics API: DBMS-independent Access and Management of DBMS Statistics in Heterogeneous Environments. In: Proc. ICEIS (2007)Google Scholar
  5. 5.
    Petkovic, M., Petkovic, L.: Complex Interval Arithmetic and Its Applications. Wiley-VCH, Chichester (1998)zbMATHGoogle Scholar
  6. 6.
    Ioannidis, Y.: The History of Histograms (abridged). In: Proc. VLDB (2003)Google Scholar
  7. 7.
    Bruno, N., Chaudhuri, S., Gravano, L.: STHoles: A Multidimensional Workload-Aware Histogram. In: Proc. SIGMOD (2001)Google Scholar
  8. 8.
    Chaudhuri, S.: An Overview of Query Optimization in Relational Systems. In: Proc. PODS (1998)Google Scholar
  9. 9.
    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)Google Scholar
  10. 10.
    Ioannidis, Y., Poosala, V.: Histogram-Based Solutions to Diverse Database Estimation Problems. Data Engineering Bulletin 18(3) (1995)Google Scholar
  11. 11.
    Ioannidis, Y., Poosala, V.: Balancing Histogram Optimality and Practicality for Query Result Size Eestimation. In: Proc. SIGMOD (1995)Google Scholar
  12. 12.
    Poosala, V., Haas, P., Ioannidis, Y., Shekita, E.: Improved Histograms for Selectivity Estimation of Range Predicates. In: Proc. SIGMOD (1996)Google Scholar
  13. 13.
    Poosala, V., Ioannidis, Y.: Selectivity Estimation Without the Attribute Value Independence Assumption. In: Proc. VLDB (1997)Google Scholar
  14. 14.
    Ioannidis, Y., Poosala, V.: Histogram-Based Approximation of Set-Valued Query-Answers. In: Proc. VLDB (1999)Google Scholar
  15. 15.
    Poosala, V., Ganti, V., Ioannidis, Y.: Approximate Query Answering using Histograms. IEEE Data Engineering Bulletin 22(4) (1999)Google Scholar
  16. 16.
    Gibbons, P., Matias, Y., Poosala, V.: Fast Incremental Maintenance of Approximate Histograms. ACM Transactions on Database Systems 27(3) (2002)Google Scholar
  17. 17.
    Aboulnaga, A., Chaudhuri, S.: Self-tuning Histograms: Building Histograms Without Looking at Data. In: Proc. SIGMOD (1999)Google Scholar
  18. 18.
    IBM Corp.: IBM DB2 Universal Database, Administration Guide: Performance, Version 8.2Google Scholar
  19. 19.
    Oracle Corp.: Oracle Database Performance Tuning Guide, 10g Release 1 (10.1) (2003)Google Scholar
  20. 20.
    Hanson, E., Kollar, L.: Statistics Used by the Query Optimizer in Microsoft SQL Server 2005. Microsoft SQL Server TechCenter  (1993)Google Scholar
  21. 21.
    Bruno, N., Chaudhuri, S.: Exploiting Statistics on Query Expressions for Optimization. In: Proc. SIGMOD (2002)Google Scholar
  22. 22.
    Garofalakis, M., Gibbons, P.: Approximate Query Processing: Taming the TeraBytes. In: Proc. VLDB (2001)Google Scholar
  23. 23.
    Grefen, P., de By, R.: A Multi-Set Extended Relational Algebra - A Formal Approach to a Practical Issue. In: Proc. ICDE (1994)Google Scholar
  24. 24.
    Garcia-Molina, H., Ullman, J., Widom, J.: Database Systems: The Complete Book. Prentice Hall PTR, Englewood Cliffs (2001)Google Scholar
  25. 25.
    TPC-H Standard Specification, Revision 2.0.0. (2002),

Copyright information

© Springer-Verlag Berlin Heidelberg 2007

Authors and Affiliations

  • Tobias Kraft
    • 1
  • Holger Schwarz
    • 1
  • Bernhard Mitschang
    • 1
  1. 1.Institute of Parallel and Distributed Systems, University of Stuttgart, Universitätsstraße 38, 70569 StuttgartGermany

Personalised recommendations