User-Optimizer Communication using Abstract Plans in Sybase ASE

Query optimizers are error prone, due to both their nature and the increased search space that modern query processing requires them to manage. This paper introduces the Sybase Adaptive Server Enterprise (ASE) Abstract Plan (AP) language, a novel technology that puts together a set of proven techniques to palliate optimizer mistaken decisions. The AP language is a 2-way user-optimizer communication mechanism based on a physical level relational algebra. AP expressions are used both by the optimizer to describe the plan that it selected and by the user to direct the optimizer choices. APs are not textually part of the query. They are persistent objects stored in the system catalogs. APs yield important performance gains by eliminating all optimizer errors.

[1]  Margaret H. Dunham,et al.  Join processing in relational databases , 1992, CSUR.

[2]  Goetz Graefe.  The Cascades Framework for Query Optimization , 1995, IEEE Data Eng. Bull..

[3]  Patrick Valduriez,et al.  On the Effectiveness of Optimization Search Strategies for Parallel Execution Spaces , 1993, VLDB.

[4]  Won Kim,et al.  On optimizing an SQL-like nested query , 1982, TODS.

[5]  Surajit Chaudhuri,et al.  Automated Selection of Materialized Views and Indexes in SQL Databases , 2000, VLDB.

[6]  Umeshwar Dayal,et al.  Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers , 1987, VLDB.

[7]  Yongwen Xu.  EFFICIENCY IN THE COLUMBIA DATABASE QUERY OPTIMIZER , 1998 .

[8]  Goetz Graefe,et al.  Optimization of dynamic query evaluation plans , 1994, SIGMOD '94.

[9]  Michael R. Genesereth,et al.  Answering recursive queries using views , 1997, PODS '97.

[10]  Surajit Chaudhuri,et al.  Maintenance of Materialized Views: Problems, Techniques, and Applications. , 1995 .

[11]  Inderpal Singh Mumick,et al.  Maintenance of Materialized Views: Problems, Techniques, and Applications , 1999, IEEE Data Eng. Bull..

[12]  Goetz Graefe,et al.  Encapsulation of Parallelism and Architecture-Independence in Extensible Database Query Execution , 1993, IEEE Trans. Software Eng..

[13]  Goetz Graefe,et al.  Encapsulation of parallelism in the Volcano query processing system , 1990, SIGMOD '90.

[14]  Tony Mason,et al.  Lex & Yacc , 1992 .

[15]  Ashish Gupta,et al.  Aggregate-Query Processing in Data Warehousing Environments , 1995, VLDB.

[16]  G. Antoshenkov,et al.  Dynamic query optimization in Rdb/VMS , 1993, Proceedings of IEEE 9th International Conference on Data Engineering.

[17]  Patrick Valduriez,et al.  Join indices , 1987, TODS.

[18]  Harry K. T. Wong,et al.  Optimization of nested SQL queries revisited , 1987, SIGMOD '87.

[19]  Karen Ward,et al.  Dynamic query evaluation plans , 1989, SIGMOD '89.

[20]  Goetz Graefe,et al.  Extensible Query Optimization and Parallel Execution in Volcano , 1991, Query Processing for Advanced Database Systems.

[21]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

[22]  Guy M. Lman.  Grammar-like Functional Rules for Representing Query Optimization Alternatives , 1998 .

[23]  Hamid Pirahesh,et al.  Starburst Mid-Flight: As the Dust Clears , 1990, IEEE Trans. Knowl. Data Eng..

[24]  Robert Kooi,et al.  The Optimization of Queries in Relational Databases , 1980 .

[25]  Sumit Ganguly,et al.  Design and Analysis of Parametric Query Optimization Algorithms , 1998, VLDB.

[26]  Patrick Valduriez,et al.  Open issues in parallel query optimization , 1996, SGMD.

[27]  Surajit Chaudhuri,et al.  An overview of query optimization in relational systems , 1998, PODS.

[28]  Per-Åke Larson,et al.  Eager Aggregation and Lazy Aggregation , 1995, VLDB.

[29]  Patrick Valduriez,et al.  Distributed and parallel database systems , 1996, CSUR.

[30]  Hamid Pirahesh,et al.  Extensible query processing in starburst , 1989, SIGMOD '89.

[31]  Luc Bouganim,et al.  A Dynamic Query Processing Architecture for Data Integration Systems , 2000, IEEE Data Eng. Bull..

[32]  Kyuseok Shim,et al.  Optimizing Queries with Aggregate Views , 1996, EDBT.

[33]  Gottfried Vossen,et al.  Query Processing for Advanced Database Systems , 1993 .

[34]  Hamid Pirahesh,et al.  Complex query decorrelation , 1996, Proceedings of the Twelfth International Conference on Data Engineering.

[35]  Goetz Graefe,et al.  Volcano - An Extensible and Parallel Query Evaluation System , 1994, IEEE Trans. Knowl. Data Eng..

[36]  C. Mohan,et al.  Interactions between query optimization and concurrency control , 1992, [1992 Proceedings] Second International Workshop on Research Issues on Data Engineering: Transaction and Query Processing.

[37]  David J. DeWitt,et al.  The EXODUS optimizer generator , 1987, SIGMOD '87.

[38]  Goetz Graefe,et al.  The Volcano optimizer generator: extensibility and efficient search , 1993, Proceedings of IEEE 9th International Conference on Data Engineering.

[39]  Panos Vassiliadis,et al.  ARKTOS: A Tool For Data Cleaning and Transformation in Data Warehouse Environments , 2000, IEEE Data Eng. Bull..

[40]  Johann-Christoph Freytag,et al.  A rule-based view of query optimization , 1987, SIGMOD '87.

[41]  Patrick Valduriez,et al.  Optimization of object-oriented recursive queries using cost-controlled strategies , 1992, SIGMOD '92.

[42]  Goetz Graefe,et al.  Query evaluation techniques for large databases , 1993, CSUR.