# US 12,602,380 B2 · Subsumption of views and subqueries

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 12,602,380 B2
- Document ID: US12602380B2
- Status: Granted
- Kind: B2
- Application number: 17/380,905
- Filed: 2021-07-20
- Published: 2023-01-26
- Granted: 2026-04-14
- Earliest claimed: 2021-07-20
- Assignees: Oracle International Corporation
- Applicants: Oracle International Corporation
- Inventors: Rafi Ahmed; Mohamed Ziauddin; Sankar Subramanian
- Web page: https://patentgrep.com/patent/US12602380B2

## CPC

- G06F 16/24535: {of sub-queries or views}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## 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.

## Claims

### Claim 1

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.

### Claim 2 (depends on 1)

The method of claim 1, wherein the replacing the plurality of inner query blocks with the subsuming view comprises:
replacing an aggregation function within a particular inner query block, of the plurality of inner query blocks with a subsuming view,
wherein the particular inner query block includes a particular SELECT clause,
wherein the subsuming view includes in a SELECT clause a CASE statement as an argument for the aggregation function, wherein the CASE statement includes a condition based on a non-factorized filter predicate.

### Claim 3 (depends on 1)

The method of claim 1, wherein the transforming the query further comprises in response to the database management system determining that the subsuming view satisfies optimization criteria, performing grouping and pre-computation of aggregation functions in a group-by view.

### Claim 4 (depends on 3)

The method of claim 3, wherein said optimization criteria comprises:
said grouping reduces a number of rows produced by the group-by view by at least a first predetermined amount;
said grouping reduces aggregation functions by at least a second predetermined amount of reduction.

### Claim 5 (depends on 4)

The method of claim 4, wherein said subsuming view includes a set of CASE statements, wherein the optimization criteria further comprises:
determining that the subsuming view refers to a single table;
determining that the subsuming view includes unified filter predicates in a WHERE clauses in the subsuming view;
determining that there are at most two columns that appear in a WHEN clause of each CASE statement of said set of CASE statements.

### Claim 6 (depends on 3)

The method of claim 3, wherein the performing grouping and precomputation of aggregation functions comprises:
creating the group-by view in the subsuming view;
wherein the group-by view contains tables, join conditions, and filter predicates of the subsuming view;
wherein a GROUP BY clause of the group-by view contains a single column referenced in a CASE statement in the subsuming view.

### Claim 7 (depends on 3)

The method of claim 3, wherein the subsuming view includes a set of aggregation functions including AVG aggregation functions, wherein each AVG aggregation function of the set of aggregation functions is replaced with a SUM aggregation function and a COUNT aggregation function.

### Claim 8

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.

### Claim 9 (depends on 8)

The one or more non-transitory computer-readable media of claim 8, wherein the replacing the plurality of inner query blocks with the subsuming view comprises:
replacing an aggregation function within a particular inner query block, of the plurality of inner query blocks with a subsuming view,
wherein the particular inner query block includes a particular SELECT clause,
wherein the subsuming view includes in a SELECT clause a CASE statement as an argument for the aggregation function, wherein the CASE statement includes a condition based on a non-factorized filter predicate.

### Claim 10 (depends on 8)

The one or more non-transitory computer-readable media of claim 8, wherein the transforming the query further comprises in response to the database management system determining that the subsuming view satisfies optimization criteria, performing grouping and pre-computation of aggregation functions in a group-by view.

### Claim 11 (depends on 10)

The one or more non-transitory computer-readable media of claim 10, wherein said optimization criteria comprises:
said grouping reduces a number of rows produced by the group-by view by at least a first predetermined amount;
said grouping reduces aggregation functions by at least a second predetermined amount of reduction.

### Claim 12 (depends on 11)

The one or more non-transitory computer-readable media of claim 11, wherein said subsuming view includes a set of CASE statements, wherein the optimization criteria further comprises:
determining that the subsuming view refers to a single table;
determining that the subsuming view includes unified filter predicates in a WHERE clauses in the subsuming view;
determining that there are at most two columns that appear in a WHEN clause of each CASE statement of said set of CASE statements.

### Claim 13 (depends on 10)

The one or more non-transitory computer-readable media of claim 10, wherein the performing grouping and precomputation of aggregation functions comprises:
creating the group-by view in the subsuming view;
wherein the group-by view contains tables, join conditions, and filter predicates of the subsuming view;
wherein a GROUP BY clause of the group-by view contains a single column referenced in a CASE statement in the subsuming view.

### Claim 14 (depends on 10)

The one or more non-transitory computer-readable media of claim 10, wherein the subsuming view includes a set of aggregation functions including AVG aggregation functions, wherein each AVG aggregation function of the set of aggregation functions is replaced with a SUM aggregation function and a COUNT aggregation function.