Query graphs, implementing trees, and freely-reorderable outerjoins
SIGMOD '90 Proceedings of the 1990 ACM SIGMOD international conference on Management of data
Algebraic optimization of outerjoin queries
Algebraic optimization of outerjoin queries
SIGMOD '94 Proceedings of the 1994 ACM SIGMOD international conference on Management of data
Outerjoin simplification and reordering for query optimization
ACM Transactions on Database Systems (TODS)
On optimizing an SQL-like nested query
ACM Transactions on Database Systems (TODS)
Extending the database relational model to capture more meaning
ACM Transactions on Database Systems (TODS)
Clio: a semi-automatic tool for schema mapping
SIGMOD '01 Proceedings of the 2001 ACM SIGMOD international conference on Management of data
Principles of Database and Knowledge-Base Systems: Volume II: The New Technologies
Principles of Database and Knowledge-Base Systems: Volume II: The New Technologies
Access path selection in a relational database management system
SIGMOD '79 Proceedings of the 1979 ACM SIGMOD international conference on Management of data
Processing queries with quantifiers a horticultural approach
PODS '83 Proceedings of the 2nd ACM SIGACT-SIGMOD symposium on Principles of database systems
How to Extend a Conventional Optimizer to Handle One- and Two-Sided Outerjoin
Proceedings of the Eighth International Conference on Data Engineering
Using EELs, a Practical Approach to Outerjoin and Antijoin Reordering
Proceedings of the 17th International Conference on Data Engineering
The Volcano Optimizer Generator: Extensibility and Efficient Search
Proceedings of the Ninth International Conference on Data Engineering
Advanced SQL 1999: Understanding Object-Relational, and Other Advanced Features
Advanced SQL 1999: Understanding Object-Relational, and Other Advanced Features
View matching for outer-join views
VLDB '05 Proceedings of the 31st international conference on Very large data bases
Full disjunctions: polynomial-delay iterators in action
VLDB '06 Proceedings of the 32nd international conference on Very large data bases
Cost-based query transformation in Oracle
VLDB '06 Proceedings of the 32nd international conference on Very large data bases
View matching for outer-join views
The VLDB Journal — The International Journal on Very Large Data Bases
ACM Computing Surveys (CSUR)
Proceedings of the VLDB Endowment
Subsumption and complementation as data fusion operators
Proceedings of the 13th International Conference on Extending Database Technology
Immediate materialized views with outerjoins
DOLAP '10 Proceedings of the ACM 13th international workshop on Data warehousing and OLAP
Declarative data fusion – syntax, semantics, and implementation
ADBIS'05 Proceedings of the 9th East European conference on Advances in Databases and Information Systems
Incremental maintenance of materialized views with outerjoins
Information Systems
On the correct and complete enumeration of the core search space
Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data
Efficient implementation of generalized quantification in relational query languages
Proceedings of the VLDB Endowment
| Hi-index | 0.00 |
Outerjoins are an important class of joins and are widely used in various kinds of applications. It is challenging to optimize queries that contain outerjoins because outerjoins do not always commute with inner joins. Previous work has studied this problem and provided techniques that allow certain reordering of the join sequences. However, the optimization of outerjoin queries is still not as powerful as that of inner joins.An inner join query can always be canonically represented as a sequence of Cartesian products of all relations, followed by a sequence of selection operations, each applying a conjunct in the join predicates. This canonical abstraction is very powerful because it enables the optimizer to use any join sequence for plan generation. Unfortunately, such a canonical abstraction for outerjoin queries has not been developed. As a result, existing techniques always exclude certain join sequences from planning, which can lead to a severe performance penalty.Given a query consisting of a sequence of inner and outer joins, we, for the first time, present a canonical abstraction based on three operations: outer Cartesian products, nullification, and best match. Like the inner join abstraction, our outerjoin abstraction permits all join sequences, and preserves the property of both commutativity and transitivity among predicates. This allows us to generate plans that are very desirable for performance reasons but that couldn't be done before. We present an algorithm that produces such a canonical abstraction, and a method that extends an inner-join optimizer to generate plans in an expanded search space. We also describe an efficient implementation of the best match operation using the OLAP functionalities in SQL:1999. Our experimental results show that our technique can significantly improve the performance of outerjoin queries.