# Algorithms for Efficient Top Down Join Enumeration

Graph-Aware Join Enumeration Algorithms In Chapter 2, we give a general
introduction to top-down join enumeration. We explain a naive approach and
give a complexity analysis that motivates three new graphpartitioning strategies.
For the ...

Author: Pit Fender

Publisher: GRIN Verlag

ISBN: 3656663432

Page: 201

View: 661

Doctoral Thesis / Dissertation from the year 2014 in the subject Computer Science - Applied, grade: summa cum laude, University of Mannheim (School of Business Informatics and Mathematics), course: Databases, language: English, abstract: For a DBMS that provides support for a declarative query language like SQL, the query optimizer is a crucial piece of software. The declarative nature of a query allows it to be translated into many equivalent evaluation plans. The process of choosing a suitable plan from all alternatives is known as query optimization. The basis of this choice are a cost model and statistics over the data. Essential for the costs of a plan is the execution order of join operations in its operator tree, since the runtime of plans with different join orders can vary by several orders of magnitude. An exhaustive search for an optimal solution over all possible operator trees is computationally infeasible. To decrease complexity, the search space must be restricted. Therefore, a well-accepted heuristic is applied: All possible bushy join trees are considered, while cross products are excluded from the search. There are two efficient approaches to identify the best plan: bottom-up and top-down join enumeration. But only the top-down approach allows for branch-and-bound pruning, which can improve compile time by several orders of magnitude, while still preserving optimality. Hence, this thesis focuses on the top-down join enumeration. In the first part, we present two efficient graph-partitioning algorithms suitable for top-down join enumeration. However, as we will see, there are two severe limitations: The proposed algorithms can handle only (1) simple (binary) join predicates and (2) inner joins. Therefore, the second part adopts one of the proposed partitioning strategies to overcome those limitations. Furthermore, we propose a more generic partitioning framework that enables every graph-partitioning algorithm to handle join predicates involving more than two relations, and outer joins as well as other non-inner joins. As we will see, our framework is more efficient than the adopted graph-partitioning algorithm. The third part of this thesis discusses the two branch-and-bound pruning strategies that can be found in the literature. We present seven advancements to the combined strategy that improve pruning (1) in terms of effectiveness, (2) in terms of robustness and (3), most importantly, avoid the worst-case behavior otherwise observed. Different experiments evaluate the performance improvements of our proposed methods. We use the TPC-H, TPC-DS and SQLite test suite benchmarks to evaluate our joined contributions.

#### Related Books:

Language: en
Pages: 201
Authors: Pit Fender
Categories: Computers
Type: BOOK - Published: 2014-06-03 - Publisher: GRIN Verlag

Doctoral Thesis / Dissertation from the year 2014 in the subject Computer Science - Applied, grade: summa cum laude, University of Mannheim (School of Business Informatics and Mathematics), course: Databases, language: English, abstract: For a DBMS that provides support for a declarative query language like SQL, the query optimizer is
Language: en
Pages: 204
Authors: Pit Fender
Categories: Computers
Type: BOOK - Published: 2014-12 - Publisher: Anchor Academic Publishing (aap_verlag)

For a DBMS that provides support for a declarative query language like SQL, the query optimizer is a crucial piece of software. The declarative nature of a query allows it to be translated into many equivalent evaluation plans. The process of choosing a suitable plan from all alternatives is known
Language: un
Pages: 208
Authors: Pit Fender
Categories: Computers
Type: BOOK - Published: 2014-06-13 - Publisher:

Doctoral Thesis / Dissertation from the year 2014 in the subject Computer Science - Applied, grade: summa cum laude, University of Mannheim (School of Business Informatics and Mathematics), course: Databases, language: English, abstract: For a DBMS that provides support for a declarative query language like SQL, the query optimizer is
Language: en
Pages: 286
Authors: ACM Special Interest Group for Algorithms and Computation Theory, Association for Computing Machinery. Special Interest Group on Management of Data
Categories: Computer science
Type: BOOK - Published: 1998 - Publisher: Assn for Computing Machinery

Books about Proceedings of the Seventeenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems
Language: en
Pages: 603
Authors: T. M. Vijayaraman
Categories: Data base management
Type: BOOK - Published: 1996 - Publisher: Morgan Kaufmann Pub

Books about Proceedings of the Twenty-second International Conference on Very Large Data Bases, Mumbai (Bombay), India, 3-6 September, 1996