Unnesting Scalar SQL Queries in the Presence of Disjunction

Brantner, Matthias; May, Norman; Moerkotte, Guido

doi:10.1109/icde.2007.367850

Cited by 9 publications

(6 citation statements)

References 15 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…In our approach, both no and all can be computed by applying the algorithms in [32] or [27] to the result of the join, or (as before) by grouping such result by the parameter ok and applying a count aggregate on each group: the result of the count is then used to filter the appropriate groups. The only caveat is that for no, outerjoin must be used, similar to unnesting [6]. We stress that either approach can handle more complex queries where several quantifiers are used (either nested within each other or at the same level).…”

Section: Computing Generalised Quantificationmentioning

confidence: 98%

“…Thus, the first subquery must be understood as if it had a correlation on.orderkey: for the purposes of evaluating the SOME operator, it lists all partkeys associated with a given orderkey. 6 This set is compared with that of the second subquery: if the intersection is not empty, then the orderkey qualifies for the answer. In other words, this query could be written in a more standard manner as follows:…”

Section: Using Gqs In Query Languagesmentioning

confidence: 99%

See 1 more Smart Citation

Supporting quantified queries in distributed databases

Badia

Dobbs

2014

International Journal of Parallel, Emergent and Distributed Sys

View full text Add to dashboard Cite

We show that some relational queries, which we call quantified queries are not well supported in distributed environments. We give a formal definition of quantified queries, propose a language in which to express said queries and provide a procedure to compute answers in this new language in the context of distributed databases. The proposed language is made up of high-level, declarative operators (called generalised quantifiers), and therefore it can be used in combination with several distributed frameworks. Our approach is designed to be as general as possible; it assumes horizontally partitioned relations, but nothing else, so no data placement or replication is used. We present an implementation and algorithms for the new language, propose some basic optimisations and give experimental results which show that the new approach is indeed quite efficient and scales well.

show abstract

Section: Computing Generalised Quantificationmentioning

confidence: 98%

Section: Using Gqs In Query Languagesmentioning

confidence: 99%

Supporting quantified queries in distributed databases

Badia

Dobbs

2014

International Journal of Parallel, Emergent and Distributed Sys

View full text Add to dashboard Cite

show abstract

“…The paper [12] suggests an interesting way of avoiding the duplication of relation R by using a bypass operator. The query is converted into a DAG where the bypass operator dispatches the rows to the branches of the DAG and a union-all at the top combines them together again.…”

Section: Complex Correlationmentioning

confidence: 99%

Execution strategies for SQL subqueries

Elhemali

Galindo-Legaria

Grabs

et al. 2007

Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data

View full text Add to dashboard Cite

Optimizing SQL subqueries has been an active area in database research and the database industry throughout the last decades. Previous work has already identified some approaches to efficiently execute relational subqueries. For satisfactory performance, proper choice of subquery execution strategies becomes even more essential today with the increase in decision support systems and automatically generated SQL, e.g., with ad-hoc reporting tools. This goes hand in hand with increasing query complexity and growing data volumes -which all pose challenges for an industrial-strength query optimizer.This current paper explores the basic building blocks that Microsoft SQL Server utilizes to optimize and execute relational subqueries. We start with indispensable prerequisites such as detection and removal of correlations for subqueries. We identify a full spectrum of fundamental subquery execution strategies such as forward and reverse lookup as well as set-based approaches, explain the different execution strategies for subqueries implemented in SQL Server, and relate them to the current state of the art. To the best of our knowledge, several strategies discussed in this paper have not been published before.An experimental evaluation complements the paper. It quantifies the performance characteristics of the different approaches and shows that indeed alternative execution strategies are needed in different circumstances, which make a cost-based query optimizer indispensable for adequate query performance.

show abstract

“…They extend the relational algebra with the so-called apply operator that is used to algebraically represent nested queries in the WHERE clause. Both Brantner et al [3] and Neumann and Kemper [17] extend the relational algebra with new operators to algebraically represent subquery unnestings. In the latter case, these operators use sideways information passing to execute nested queries more efficiently.…”

Section: Related Workmentioning

confidence: 99%

“…These techniques either work at the level of SQL [14,20], define new operators [3,9] and algebras [4] that are specifically targeted at nested queries, or build on entirely different formalisms, e.g., comprehension calculus [8,12,13]. We argue that these approaches all have drawbacks that have limited their adoption in database systems.…”

Section: Introductionmentioning

confidence: 99%

Optimization of Nested Queries using the NF2 Algebra

Hölsch

Grossniklaus

Scholl

2016

Proceedings of the 2016 International Conference on Management of Data

View full text Add to dashboard Cite

A key promise of SQL is that the optimizer will find the most efficient execution plan, regardless of how the query is formulated. In general, query optimizers of modern database systems are able to keep this promise, with the notable exception of nested queries. While several optimization techniques for nested queries have been proposed, their adoption in practice has been limited. In this paper, we argue that the NF 2 (non-first normal form) algebra, which was originally designed to process nested tables, is a better approach to nested query optimization as it fulfills two key requirements. First, the NF 2 algebra can represent all types of nested queries as well as both existing and novel optimization techniques based on its equivalences. Second, performance benefits can be achieved with little changes to existing transformation-based query optimizers as the NF 2 algebra is an extension of the relational algebra.

show abstract

Unnesting Scalar SQL Queries in the Presence of Disjunction

Cited by 9 publications

References 15 publications

Supporting quantified queries in distributed databases

Supporting quantified queries in distributed databases

Execution strategies for SQL subqueries

Optimization of Nested Queries using the NF2 Algebra

Contact Info

Product

Resources

About