A Variability Model for Query Optimizers

1. A Variability Model for Query Optimizers Michael Soffner1, Norbert Siegmund1, Marko Rosenmüller1, Janet Siegmund1, Thomas Leich2, Gunter Saake1 1 University of Magdeburg, Germany 2 METOP GmbH, Germany

2. Outline • Motivation • Variability Approach • System Analysis • Unified Variability Model

3. Motivation • Database vendors continuously extend functionality to fit to new application domains • Leads to over bloated systems that have decreased performance and manageability • Specialized systems outperform RDBMS, e.g., Sensor Networks and Data Warehouses (Stonebraker2005) Driving factors for Query Optimizer extensions • SQL  conformity to standard • New indexes, operations, statistics Result: Increased search space and reduced performance

4. Our Approach • Goal: Specialized query processors by introducing variability • Selection of only needed functionality and omitting the rest • Variability through Software Product Lines (SPLs) Fig.1 Benefits of tailored Query Optimizers

5. Software Product Lines (SPLs) Use Features to describe a concept in a domain model

6. Product Derivation Feature Model Reusable Implementation Artifacts Domain Engineering Application Engineering Configuration Program Generator Final Product

7. Overall Process • 3 Steps to a unified model Course Model SystemAnalysis Unification Oracle, PostgreSQL, SQLite Classification by Jarke (1984) Unified Model

8. Optimizer Functionality Classification (Jarke) • Generally distinguishes logical and physical optimization

9. SQLite • Customizable through #ifdef compiler flags  static configuration • All logical optimization features are optional • Only B-Tree indexes • Allows statistics to be omitted statically

10. PostgreSQL • Most logical optimization feature aim to standardize the input query • No features for special heuristics • Includes inline set returning functions • Two evaluation algorithms: exhaustive search, genetic algorithm • Four index types: b-tree, hash-based and multi-dimension-based indexes (GIS support)

11. Oracle • Special feature: predicates pushing, rewrite materialized views • Most Access Paths • Configuration through Hints

12. Variability Model: Unification Process • Goal: System-independent Variability Model • Identification of feature that implement same functionality • Integration • A1: Same functionality but different names • A2: Same names but different functionality • Only semantic descriptions allow a decision • Basis: Documentation and Source Code • Example: Nested Loop

13. Variability Model: Unification Process 2 • Unification • 1:1 Mapping (Mapping of one Features into one unified Feature) • 1:n Mapping (Compose multiple system-dependent Features into one unified Feature)

14. Variability Model

15. Conclusion • Provide a basis for implementing configurable query optimizer • Unified semantic description of query optimizer functionality (Taxonomy/Ontology) • Provides a foundation for a (semi-)automatic configuration of query optimizers based on application requirements • Provide a basis for modeling dependencies between query optimizers and deeper layers of DBMSs, e.g., Storage Engine

16. References [Stonebraker2005] M. Stonebraker and U. Cetintemel. One Size Fits All: An Idea Whose Time Has Come and Gone. In Proceedings of the International Conference on Data Engineering (ICDE),pages 2-11, 2005. [Jarke84] M. Jarke and J. Koch. Query optimization in database systems. ACM Computing Surveys (CSUR), 16:111-152, June 1984. ACM ID: 356928.

17. Thanks for your attention!