SQL query optimization through nested relational algebra

Cao, Bin; Badia, Antonio

doi:10.1145/1272743.1272748

Cited by 15 publications

(17 citation statements)

References 50 publications

(94 reference statements)

Supporting

Mentioning

Contrasting

Order By: Relevance

“…In the latter case, these operators use sideways information passing to execute nested queries more efficiently. Similar to our work, the approaches of Wang et al [19] and Cao and Badia [4] also propose the use of nested relational algebras, but assume the presence of physical nest and unnest operators. Compared to these approaches of the second category, our approach performs only logical optimizations and every logical operator that we use can be mapped to a physical operator found in any current database system.…”

Section: Related Workmentioning

confidence: 63%

See 1 more Smart Citation

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

Section: Related Workmentioning

confidence: 63%

“…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

show abstract

“…Some systems, like Rhizomer [18] and Etable [60], can display parallel one-to-many relationships, but only at a single level of nesting. Besides their use in visual query systems, nested data models have been used both in optimization [97,20] and expressiveness analysis [70] of query languages with aggregate functions.…”

Section: Visual Query Systemsmentioning

confidence: 99%

Expressive Query Construction through Direct Manipulation of Nested Relational Results

Bakke

Karger

2016

Proceedings of the 2016 International Conference on Management of Data

View full text Add to dashboard Cite

Despite extensive research on visual query systems, the standard way to interact with relational databases remains to be through SQL queries and tailored form interfaces. This makes the power of relational databases largely inaccessible to non-programmers. This thesis proposes a solution, in two parts.The first contribution of this thesis is a solution to the visual query language problem, that is, the problem of letting end users construct arbitrary database queries through a graphical user interface. We propose the first visual query language to simultaneously satisfy three requirements: (1) query specification through direct manipulation of results, (2) the ability to view and modify any part of the current query without departing from the direct manipulation interface, and (3) SQL-like expressiveness. By directly manipulating nested relational results, and using spreadsheet idioms such as formulas and filters, the user can express arbitrary SQL-92 queries while always remaining able to track and modify the state of the complete query.The second contribution of this thesis is an algorithm for automatically formatting nested relational data using the traditional visual idioms of hand-designed database UIs: tables, multi-column forms, and outline-style indented lists. The algorithm plugs directly into the output stage of our visual query language, and produces the concrete graphics that the user sees and manipulates on the screen during query construction. The algorithm eliminates the need for an application developer to specify low-level presentation details such as label placements, text field dimensions, table column widths, and list styles.Our prototype visual query system gives the user an experience of responsive, incremental query building while pushing all actual query processing to the database layer. We evaluate the query building aspects of our system with formative and controlled user studies on a total of 28 spreadsheet users. The controlled study shows our system outperforming Microsoft Access by 18 points on the System Usability Scale [17]; this corresponds to a 46 percentage point difference on a percentile scale of other studies in the Business Software category. We also evaluate the different layouts that can be produced by our automatic layout algorithm, including via an online user study on 27 subjects.

show abstract

“…In query q2 the ALL-sublink is also reqtrue. For tuple (4,5), the only tuple that fulfills condition C, all tuples from relation R are included in the provenance of (4,5). In Query q3 the sublink is reqfalse for input tuple (2, 1) and reqind for input tuple (3, 2).…”

Section: Examplesmentioning

confidence: 99%

“…4 The main difference is that different tuples from the input of the operator can produce the same result tuple. The set of tuples from the input T that produce a tuple t from the output is the provenance T * of t according to T (see [4,12]). Let w.l.o.g.…”

Section: Single Sublinks In Projectionsmentioning

confidence: 99%

Provenance for nested subqueries

Glavic

Alonso

2009

Proceedings of the 12th International Conference on Extending Database Technology: Advances in Database Technology

View full text Add to dashboard Cite

Data provenance is essential in applications such as scientific computing, curated databases, and data warehouses. Several systems have been developed that provide provenance functionality for the relational data model. These systems support only a subset of SQL, a severe limitation in practice since most of the application domains that benefit from provenance information use complex queries. Such queries typically involve nested subqueries, aggregation and/or user defined functions. Without support for these constructs, a provenance management system is of limited use.In this paper we address this limitation by exploring the problem of provenance derivation when complex queries are involved. More precisely, we demonstrate that the widely used definition of Why-provenance fails in the presence of nested subqueries, and show how the definition can be modified to produce meaningful results for nested subqueries. We further present query rewrite rules to transform an SQL query into a query propagating provenance. The solution introduced in this paper allows us to track provenance information for a far wider subset of SQL than any of the existing approaches. We have incorporated these ideas into the Perm provenance management system engine and used it to evaluate the feasibility and performance of our approach. Provenance for Nested Subqueries ABSTRACTData provenance is essential in applications such as scientific computing, curated databases, and data warehouses. Several systems have been developed that provide provenance functionality for the relational data model. These systems support only a subset of SQL, a severe limitation in practice since most of the application domains that benefit from provenance information use complex queries. Such queries typically involve nested subqueries, aggregation and/or user defined functions. Without support for these constructs, a provenance management system is of limited use.In this paper we address this limitation by exploring the problem of provenance derivation when complex queries are involved. More precisely, we demonstrate that the widely used definition of Why-provenance fails in the presence of nested subqueries, and show how the definition can be modified to produce meaningful results for nested subqueries. We further present query rewrite rules to transform an SQL query into a query propagating provenance. The solution introduced in this paper allows us to track provenance information for a far wider subset of SQL than any of the existing approaches. We have incorporated these ideas into the Perm provenance management system engine and used it to evaluate the feasibility and performance of our approach.

show abstract

SQL query optimization through nested relational algebra

Cited by 15 publications

References 50 publications

Optimization of Nested Queries using the NF2 Algebra

Optimization of Nested Queries using the NF2 Algebra

Expressive Query Construction through Direct Manipulation of Nested Relational Results

Provenance for nested subqueries

Contact Info

Product

Resources

About