Tableau Software, Inc.

Sap Se

Jan Finis

Tobias Muehlbauer

Thomas Neumann

Alfons Kemper

Viktor Leis

Robert Brunel

Manuel Then

Adrian Vogelsgesang

Michael Schreier

Richard Lee Cole

Sebastian Hofstetter

Moritz Kaufmann

Michael Haubenschild

Gerald Franz

Richard Wesley

Pawel Terlecki

Martin Raiber

A method executes at a computer system to retrieve data from a database. Upon receiving a database query, the computer system translates the query into an intermediate representation, and estimates a compilation time to compile the intermediate representation into machine executable code. The query execution time to retrieve a result set is also estimated. In accordance with a determination that the query execution time and compilation time satisfy an interpretation criterion, the computer system invokes a byte code interpreter to interpret the intermediate representation and retrieve the result set from the database. In accordance with a determination that the query execution and compilation times satisfy one of a plurality of compilation criteria, the computer system compiles the intermediate representation to form machine code and executes the machine code to retrieve the result set from the database. In some cases, the query intermediate representation is optimized prior to compilation.

Adaptive interpretation and compilation of database queries

A method is executed at a computer system to retrieve data from a database. Upon receiving a database query, a database engine of the computer system parses the query to form an operator tree including a plurality of join operators. For one of the plurality of clauses, the database engine adds to the operator tree a respective node that specifies a mark join operator, a single join operator, an inner join operator, or an outer join operator. Specifically, the database engine adds the mark join operator when the clause includes one of a predetermined set of predicate subqueries, and adds the single join operator when the clause includes a scalar subquery. The database engine performs one or more optimization passes on the operator tree to form an optimized execution plan, and executes the optimized execution plan to retrieve a result set from the database.

Optimizing database query execution by extending the relational algebra to include non-standard join operators

A method dynamically selects query execution operators. A database engine receives a query, parses the query to form a query execution tree, and compiles the tree to form a first executable plan that includes in-memory operators. The database engine executes the first plan, including executing in-memory operators in parallel. While executing a first in-memory operator, insufficient memory is detected. In response, the database engine aborts the execution, and recompiles the query tree in two ways, forming a second executable plan that replaces the first in-memory operator with a first spooling operator. The first spooling operator executes within a fixed volatile memory budget and swaps to non-volatile memory according to the budget. A third executable plan retains the first in-memory operator, but schedules it to run serially. The database engine selects either the second plan or the third plan, and executes the selected plan to return results for the query.

Dynamic rebuilding of query execution trees and reselection of query execution operators

A method implements optimization of database queries by computing domain cardinality estimates. A client sends a database query to a server. The method parses the query to identify data columns. For each of the data columns, the method computes a lower bound and an upper bound of distinct data values using a pre-computed table size. The method also computes a patch factor by applying a pre-computed function to a ratio between a number of distinct data values that appear exactly once in a data sample and a number of distinct data values in the sample. Based on the patch factor, the lower bound, and the upper bound, the method computes an estimate of distinct values for each of the data columns. The method subsequently generates an execution plan for the query according to the computed estimates, executes the execution plan, and returns a result set to the client.

Computing domain cardinality estimates for optimizing database query execution

A database engine includes one or more computing devices, each having one or more processors and memory. The memory stores programs configured for execution by the processors. The database engine receives a database query from a client, and parses the database query to build a query operator tree. The query operator tree includes a plurality of query operators. The database engine performs one or more optimization passes on the query operator tree, including a deduplication optimization pass, to form an optimized execution plan. The deduplication optimization pass includes determining that a first query operator is equivalent to a second query operator during a traversal of the query operator tree, and replacing the second query operator with a link to reuse results from the first query operator. The database engine executes the optimized execution plan to retrieve a result set from the database and returns the result set to the client.

Elimination of query fragment duplication in complex database queries

A method is executed at a computer system to retrieve data from a database. Upon receiving a database query, a database engine of the computer system parses the query to form an operator tree including a plurality of join operators. For each of the plurality of clauses, the database engine adds to the operator tree a respective node that specifies a mark join operator, a single join operator, an inner join operator, or an outer join operator. Specifically, the database engine adds the mark join operator when the respective clause includes one of a predetermined set of predicate subqueries, and adds the single join operator when the respective clause includes a scalar subquery. The database engine performs one or more optimization passes on the operator tree to form an optimized execution plan, and executes the optimized execution plan to retrieve a result set from the database.

A database engine receives a database query that specifies retrieving data from a data source. The database engine parses the query to build an operator tree that includes a TableScan operator configured to scan a table from the data source to produce outputs corresponding to a single data field from the table, and includes a GroupBy operator that groups rows of the table according to the data field. The database engine generates and executes code corresponding to the operator tree to retrieve a result set. When the TableScan operator is a child of the GroupBy operator and the outputs are independent of duplicate input rows from the table, execution of the TableScan operator comprises, for each storage block of rows from the table: determining a storage compression scheme for encoding the data field and, for certain encodings, using the encoding to produce the outputs without duplication of rows.

Optimizing domain queries for relational databases

A system includes determination, based on a plurality of rows of a table, where each of the plurality of rows associated with a respective row_id represents a respective a node of a hierarchy, row_id pairs of each parent node-child node pair of the hierarchy, execution, based on the row_id pairs, of a depth-first traversal of the hierarchy to sequentially add each row_id of the plurality of rows to a hierarchy index and receive a NODE value associated with the added row_id, and, for each of the plurality of rows, update of a row based on the NODE value associated with the row_id of the row.

Derivation of hierarchy indexes

A database engine receives a human-readable database query that includes a plurality of conditional expressions. The database engine parses the database query to build an operator tree that includes a subtree corresponding to each of the conditional expressions. The database engine identifies a subexpression that appears in two or more of the conditional expressions. The subexpression is executed conditionally. The database engine hoists the subexpression outside of the conditional expression so that it is executed unconditionally. The database engine modifies the operator tree to specify computation of a value for the subexpression a first time and to reuse the computed value when the subexpression is subsequently encountered. The database engine executes the modified operator tree to form a result set corresponding to the database query, thereby evaluating the subexpression only a single time for each row of input data and returns the result set.

Elimination of common subexpressions in complex database queries

A database engine receives a query and parses the query to form a first intermediate query. The engine compiles the first intermediate query to form a first executable plan that includes in-memory operators that execute within memory without swapping to secondary memory. While executing a first in-memory operator in the first executable plan, the engine detects insufficient memory and aborts execution of the first executable plan. The engine optimizes the first intermediate query to form a second intermediate query, and compiles the second intermediate query to form a second executable plan. The second plan includes spooling operators that execute within fixed memory budgets and are configured to swap to the secondary memory when needed. The engine executes the second executable plan, including the spooling operators, to retrieve results from the database that are responsive to the query. The engine then returns the retrieved results.

A database engine receives a database query from a client. The database engine parses the database query to build a query operator tree that includes a plurality of query operators. The database engine performs one or more optimization passes on the query operator tree, including a deduplication optimization pass, to form an optimized execution plan. The deduplication optimization pass includes: creating a list of query operators via a first traversal of the query operator tree, determining a first query operator that is equivalent to a second query operator, based on a hash map, via a second traversal of the query operator tree, and substituting, via a third traversal of the query operator tree, the second query operator with a tree node that links to the first query operator. The database engine executes the optimized execution plan to retrieve a result set from the database, and returns the result set.

Growing community of inventors

Munich, Germany

Jan Finis