Optimizing database queries using subquery composition
First Claim
1. A computer implemented method for optimizing database queries, the method comprising:
- receiving, by a database system, a request to optimize a database query;
identifying an inner subquery and an outer subquery in the database query, the inner subquery nested within the outer subquery;
identifying a first set of columns such that the inner subquery performs a group by based on the first set of columns;
identifying a second set of columns such that the outer subquery performs a group by based on the second set of columns;
determining that the second set of columns is a subset of the first set of columns;
determining whether a result of the inner subquery is provided as an input to the outer subquery;
generating a combined database query corresponding to a composition of the outer subquery and the inner subquery if the result of the inner subquery is determined to be an input to the outer subquery and the second set of columns is determined to be a subset of the first set of columns; and
storing the combined database query.
6 Assignments
0 Petitions
Accused Products
Abstract
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.
83 Citations
16 Claims
-
1. A computer implemented method for optimizing database queries, the method comprising:
-
receiving, by a database system, a request to optimize a database query; identifying an inner subquery and an outer subquery in the database query, the inner subquery nested within the outer subquery; identifying a first set of columns such that the inner subquery performs a group by based on the first set of columns; identifying a second set of columns such that the outer subquery performs a group by based on the second set of columns; determining that the second set of columns is a subset of the first set of columns; determining whether a result of the inner subquery is provided as an input to the outer subquery; generating a combined database query corresponding to a composition of the outer subquery and the inner subquery if the result of the inner subquery is determined to be an input to the outer subquery and the second set of columns is determined to be a subset of the first set of columns; and storing the combined database query. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
-
8. A computer-readable non-transitory storage medium storing computer-executable code for optimizing queries of a database system, the code, when executed by a processor, causing the processor to:
-
receive a request to optimize a database query; identify an inner subquery and an outer subquery in the database query, the inner subquery nested within the outer subquery; identifying a first set of columns such that the inner subquery performs a group by based on the first set of columns; identifying a second set of columns such that the outer subquery performs a group by based on the second set of columns; determining that the second set of columns is a subset of the first set of columns; determine whether a result of the inner subquery is provided as an input to the outer subquery; generate a combined database query corresponding to a composition of the outer subquery and the inner subquery if the result of the inner subquery is determined to be an input to the outer subquery and the second set of columns is determined to be a subset of the first set of columns; and store the combined database query. - View Dependent Claims (9, 10, 11, 12)
-
-
13. A computer implemented method for optimizing database queries, the method comprising:
-
receiving, by a database system, a request to optimize a database query; identifying an inner subquery and an outer subquery in the database query, the inner subquery nested within the outer subquery; determining that the inner subquery specifies a first where clause that computes a first set of values; determining that the outer subquery specifies a second where clause that computes a second set of values; comparing the first set of values with the second set of values; and determining that the second set of values is a subset of the first set of values; determining whether a result of the inner subquery is provided as an input to the outer subquery; generating a combined database query corresponding to a composition of the outer subquery and the inner subquery if the result of the inner subquery is determined to be an input to the outer subquery and the second set of values is determined to be a subset of the first set of values, the combined database query having the second where clause; and storing the combined database query. - View Dependent Claims (14, 15, 16)
-
Specification