Materialized view rewrite technique for one-sided outer-join queries

Patent metadata

Field	Value
Publication number	US-2021124742-A1
Application number	US-201916667527-A
Country	US
Kind code	A1
Filing date	Oct 29, 2019
Priority date	Oct 29, 2019
Publication date	Apr 29, 2021
Grant date	—

Abstract

A one-sided outer-join-based query is rewritten using a materialized view (MV), the definition of which includes the join but does not include a filter predicate from the query. The rewritten query nullifies data from the include-matching table, included in the MV, that does not satisfy the filter predicate. To improve accuracy of the query results, certain rows are removed from intermediate results of the query. To facilitate revising the query results for accuracy, the MV includes a unique column from the include-all table and also an indicator column that indicates whether a given row of the MV is an inner-join-type row or an anti-join-type row. The rewritten query adjusts the indicator values, in the indicator column, of MV rows that do not satisfy the filter to reflect a modified anti-join-type indicator value. Based on the modified indicator values and unique columns from include-all tables, accuracy of query results is attained.

First claim

What is claimed is:

1 . A computer-executed method comprising:
storing, by a database management system, a materialized view that is based on a materialized view definition that involves a one-sided outer-join operation over an include-all table and an include-matching table; after storing the materialized view, executing, by the database management system, a query over a plurality of tables in a database managed by the database management system; wherein the query involves the one-sided outer-join operation over the include-all table and the include-matching table; wherein the query further involves a filter, over the include-matching table, that is not included in the materialized view definition; wherein the database management system executing the query comprises:
rewriting the query to produce a rewritten query that:
produces an intermediate results set, wherein producing the intermediate results set comprises applying the filter to data, from the materialized view, that originated from the include-matching table, and
retrieves data from the intermediate results set; and

executing the rewritten query;
wherein the method is performed by one or more computing devices.

2 . The computer-executed method of claim 1 , wherein:
the materialized view comprises a particular plurality of rows that corresponds to a particular row of the include-all table; executing the rewritten query comprises:
determining, for each row of the particular plurality of rows, whether said each row satisfies the filter;
if all rows, of the particular plurality of rows, do not satisfy the filter, preserving only one row, of the particular plurality of rows, in a result set of the rewritten query; and
if one or more rows, of the particular plurality of rows, satisfy the filter, preserving the one or more rows, of the particular plurality of rows, in a result set of the rewritten query.

3 . The computer-executed method of claim 1 , wherein:
the materialized view comprises a plurality of rows; the materialized view includes an indicator column that stores indicator values that indicate one of (a) an inner-join-type row, or (b) an anti-join-type row; executing the rewritten query comprises, for each row of the plurality of rows:
determining whether an indicator value, in the indicator column, included in said each row indicates that said each row is an inner-join-type row,
based on determining that the indicator value indicates that said each row is an inner-join-type row:
determining whether said each row satisfies the filter;
based on determining that said each row does not satisfy the filter:
performing one or more modified anti join actions that comprise changing the indicator value, in a row, corresponding to said each row, in the intermediate results set, to reflect that said each row is a modified anti-join-type row.

4 . The computer-executed method of claim 3 , wherein performing the one or more modified anti join actions further comprise one or more of:
nullifying one or more columns, of a particular row of the plurality of rows, that come from the include-matching table; and nullifying one or more aggregate function values defined for one or more columns that come from the include-matching table.

5 . The computer-executed method of claim 3 , wherein executing the rewritten query comprises:
partitioning the intermediate results set based on a unique column of the include-all table to produce a plurality of partitions of the intermediate results set; performing one or more marking actions by, for each partition of the plurality of partitions: if said each partition includes only modified anti join rows, marking a single row of said each partition; and filtering, from the intermediate results set, modified anti join rows that are unmarked.

6 . The computer-executed method of claim 5 , wherein:
indicator values, in the indicator column, are ordered such that a modified anti-join-type indicator value sorts to an extreme of a list of indicator values that includes the modified anti join indicator value, an inner-join-type indicator value, and an anti-join-type indicator value; the method further comprises:
ordering rows in each partition, of the plurality of partitions, based on indicator values of the rows in said each partition such that modified anti-join-type indicator values are sorted to the extreme of said each partition,
wherein the one or more marking actions further comprises using an analytic function to mark an extreme row of each partition, of the plurality of partitions.

7 . The computer-executed method of claim 3 , wherein the indicator column is an inner-join indicator column.

8 . The computer-executed method of claim 1 , wherein:
the materialized view comprises a plurality of rows; the materialized view includes an indicator column that stores indicator values that indicate one of (a) an inner-join-type row, or (b) an anti-join-type row; executing the rewritten query comprises, for each row of the plurality of rows:
determining whether an indicator value, in the indicator column, included in said each row indicates that said each row is an inner-join-type row,
based on determining that the indicator value included in said each row indicates that said each row is an inner-join-type row:
determining whether said each row satisfies the filter;
based on determining that said each row does not satisfy the filter, performing one or more of:
nullifying one or more columns, of said each row, that come from the include-matching table, and
nullifying one or more aggregate functions defined for the one or more columns that come from the include-matching table.

9 . The computer-executed method of claim 1 , wherein the query further involves a second filter over the include-all table.

10 . The computer-executed method of claim 1 , wherein the query further involves one or more additional include-all tables.

11 . The computer-executed method of claim 1 , wherein the query further involves one or more additional include-matching tables.

12 . One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
storing, by a database management system, a materialized view that is based on a materialized view definition that involves a one-sided outer-join operation over an include-all table and an include-matching table; after storing the materialized view, executing, by the database management system, a query over a plurality of tables in a database managed by the database management system; wherein the query involves the one-sided outer-join operation over the include-all table and the include-matching table; wherein the query further involves a filter, over the include-matching table, that is not included in the materialized view definition; wherein the database management system executing the query comprises:
rewriting the query to produce a rewritten query that:
produces an intermediate results set, wherein producing the intermediate results set comprises applying the filter to data, from the materialized view, that originated from the include-matching table, and
retrieves data from the intermediate results set; and

executing the rewritten query.

13 . The one or more non-transitory computer-readable media of claim 12 , wherein:
the materialized view comprises a particular plurality of rows that corresponds to a particular row of the include-all table; executing the rewritten query comprises:
determining, for each row of the particular plurality of rows, whether said each row satisfies the filter;
if all ro

Assignees

• Oracle Int Corp

Inventors

• Ahmed Rafi
• Bello Randall
• Witkowski Andrew

Classifications

• G06F16/24544

  Join order optimisation · CPC title

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/24534 Primary

  Query rewriting; Transformation · CPC title

• G06F16/24526

  Internal representations for queries · CPC title

• G06F16/242 Primary

  Query formulation · CPC title