Paraccel LLC

Teradata Us, Inc.

Actian Corporation

William Joseph McKenna

Richard Lee Cole

Yijou Chen

Eugene Szedenits, Jr

Sergei Perfilov

Aman Sinha

Ahmad Said Ghazal

Selecting a join plan for a query containing a join and a union block includes determining whether to propose a join plan with the join pushed across the union block. A selection is made between a join plan in which the join is not pushed across the union block and any proposed join plan in which the join is pushed across the union block.

Pushing joins across a union

A database system determines execution plans for database queries by evaluating a number of partial solutions for each database query. The database system determines a partial solutions limit on the number of partial solutions to be evaluated for determining the execution plan of the query. The database system determines a plurality of partial solutions, each partial solution corresponding to a portion of the execution plan for processing the database query. The database system evaluates a number of candidate partial solutions for determining a partial solution. The number of candidate partial solutions evaluated is determined based on the partial solutions limit. The database system combines the plurality of partial solutions to obtain an execution plan for the database query. The database system executes the database query by executing the execution plan.

Physical planning of database queries using partial solutions

A database system rewrites database queries based on rewrite constraints specification. The rewrite constraints specification provides directives to the database system to generate temporary tables for storing intermediate results of database queries and rewrite the corresponding database queries in terms of the temporary tables. The subqueries for matching the rewrite constraints specification may be specified in various ways. For example, the rewrite constraints specification may identify the subqueries by using one or more aliases. The rewrite constraints specification may specify a list of tables such that all database queries that join the database tables specified in the rewrite constraints specification are determined to match. The optimizer is able to optimize the rewritten database queries better by getting a better estimate for the statistics describing the temporary tables.

Rewrite constraints for database queries

A database system allows users to specify plan constraint specification that limit choices of execution plans considered by an optimizer of the database system for optimizing database queries. The plan constraint specification specifies attributes of execution plans. The plan constraint specification may identify tables/intermediate results that the plan constraint is applicable to using table names or aliases. The database system applies the plan constraint specification to database queries that match the tables/aliases specified in the plan constraint specification. The database system limits the choice of execution plans evaluated for a database query to execution plans satisfying the attributes specified in an applicable plan constraint specification. The plan constraint specification may specify distribution of a table, a join order for tables, the join mechanism, cardinality of a table or intermediate result, and so on.

Limiting plan choice for database queries using plan constraints

A system and/or method are implemented to process queries to a database. In particular, the processing of queries to the database is enhanced by enhancing the determination of join orders of tables implicated in queries. Join orders between relatively large numbers of tables are determined by dividing the set of tables to be ordered into a plurality of subsets of tables, and ordering the individual subsets of tables.

System and method for processing database queries

A database system optimizes database queries that comprise subqueries. The database system factors out computation from two or more subqueries to a reusable subquery. The reusable subquery returns a result set. The subqueries are rewritten to use the result set and the database query modified to use the rewritten subqueries. The modified database query performs efficiently since the result set is computed only once for each subquery that uses the reusable query. The database system also composes nested subqueries that compute aggregate values. The database system identifies an inner subquery nested within an outer subquery, each subquery computing an aggregate value. The database system determines a new aggregate function corresponding to a composition of aggregate functions of the inner and the outer queries. The database system determines a composed query using the inner query with the outer query. The composed query uses the new aggregate function.

Growing community of inventors

Oceanside, CA, United States of America

William Joseph McKenna