US 12,602,380 B2Granted

Subsumption of views and subqueries

AssigneeOracle International Corporation

InventorsRafi Ahmed, Mohamed Ziauddin, Sankar Subramanian

Life of this patent

Estimating the term…

Loading patent…

Abstract

Techniques for subsumption of inline views and subqueries in a query are described. An optimization technique of subsumption is enabled by inline views having identical tables and identical join conditions and having aggregation functions but no group-by clauses. When subsumption takes place, a single query block replaces the inline views (or subqueries) with a single inline view query block. Subsumption reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. The single query block includes factored out filter predicates and unified predicates that originate from the subsumed inline views (or subqueries). Based on similarities among the aggregation functions and filter predicates in the subsumed inline views, pre-computation of common aggregates may be performed in a new group-by view in the subsuming view.

Claims14

Dependent claims

A computer-executed method, comprising:

a database management system transforming a query to produce a transformed query;
wherein the query includes an outer query block, the outer query block includes a plurality of inner query blocks, each inner query block of the plurality of inner query blocks being a subquery or inline view of the outer query block and including a SELECT clause and a FROM clause;
wherein the plurality of inner query blocks is in a FROM clause or a SELECT clause of the outer query block, each inner query block of said plurality of inner query blocks returning only one row;
wherein the transforming the query comprises:
determining, by the database management system, that the outer query block satisfies subsumption conditions, wherein the determining that the outer query block satisfies the subsumption conditions comprises:
determining that each of the plurality of inner query blocks includes aggregation functions and no GROUP BY clauses;
determining that the plurality of inner query blocks reference identical tables and includes one or more identical join conditions;
in response to the database management system determining that the outer query block satisfies the subsumption conditions, performing removal of the plurality of inner query blocks,
wherein the performing removal of the plurality of inner query blocks comprises replacing the plurality of inner query blocks with a subsuming view, said subsuming view including a SELECT clause, a FROM clause, wherein the replacing the plurality of inner query blocks with the subsuming view further comprises:
removing a common filter predicate from the plurality of inner query blocks and adding the common filter predicate in a WHERE clause of the subsuming view;
combining similar filter predicates from the plurality of inner query blocks into a disjunctive predicate or range filter predicate in the subsuming view;
and
placing the one or more join conditions within said subsuming view;
and
executing the transformed query;
wherein the method is performed by one or more computing devices.

13 more elements

One or more non-transitory computer-readable media storing one or more sequences of instructions that, when executed by one or more processors, cause:

a database management system transforming a query to produce a transformed query;
wherein the query includes an outer query block, the outer query block includes a plurality of inner query blocks, each inner query block of the plurality of inner query blocks being a subquery or inline view of the outer query block and including a SELECT clause and a FROM clause;
wherein the plurality of inner query blocks is in a FROM clause or a SELECT clause of the outer query block, each inner query block of said plurality of inner query blocks returning only one row;
wherein the transforming the query comprises:
determining, by the database management system, that the outer query block satisfies subsumption conditions, wherein the determining that the outer query block satisfies the subsumption conditions comprises:
determining that each of the plurality of inner query blocks includes aggregation functions and no GROUP BY clauses;
determining that the plurality of inner query blocks reference identical tables and includes one or more identical join conditions;
in response to the database management system determining that the outer query block satisfies the subsumption conditions, performing removal of the plurality of inner query blocks,
wherein the performing removal of the plurality of inner query blocks comprises replacing the plurality of inner query blocks with a subsuming view, wherein the replacing the plurality of inner query blocks with the subsuming view further comprises:
removing a common filter predicate from the plurality of inner query blocks and adding the common filter predicate in a WHERE clause of the subsuming view;
combining similar filter predicates from the plurality of inner query blocks into a disjunctive predicate or range filter predicate in the subsuming view;
and
placing the one or more join conditions within said subsuming view;
and
executing the transformed query.

12 more elements

Dependencies are inferred from the claim text.