Materialized View Rewrite Technique for One-Sided Outer-Join Queries

1. A computer-executed method comprising: storing, by a database management system, a materialized view that is based on a materialized view definition that includes a one-sided outer-join operation, with one or more join predicates, over an include-all table and an include-matching table; wherein the materialized view includes all rows of the include-all table; wherein the materialized view includes one or more rows, of the include-matching table, that satisfy the one or more join predicates; after storing the materialized view, receiving, by the database management system, a query over a plurality of tables in a database managed by the database management system; wherein the query includes: the one-sided outer-join operation over the include-all table and the include-matching table, and a filter, over the include-matching table, that is not included in the materialized view definition; in response to determining that the query includes the one-sided join operation, 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 the one or more rows included in the materialized view, and retrieves data from the intermediate results set; and executing the rewritten query to: apply the filter to the one or more rows included in the materialized view to produce the intermediate results set, and produce a results set based on data retrieved from the intermediate results set; 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; said 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; said 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 said 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 said 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-type rows, marking a single row of said each partition; and filtering, from the intermediate results set, modified anti-join-type 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-type 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; said 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 includes a second filter over the include-all table.

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

11. The computer-executed method of claim 1 , wherein the query further includes 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 includes a one-sided outer-join operation, with one or more join predicates, over an include-all table and an include-matching table; wherein the materialized view includes all rows of the include-all table; wherein the materialized view includes one or more rows, of the include-matching table, that satisfy the one or more join predicates; after storing the materialized view, receiving, by the database management system, a query over a plurality of tables in a database managed by the database management system; wherein the query includes: the one-sided outer-join operation over the include-all table and the include-matching table, and a filter, over the include-matching table, that is not included in the materialized view definition; in response to determining that the query includes the one-sided join operation, 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 the one or more rows included in the materialized view, and retrieves data from the intermediate results set; and executing the rewritten query to: apply the filter to the one or more rows included in the materialized view to produce the intermediate results set, and produce a results set based on data retrieved from the intermediate results set.

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; said 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.

14. The one or more non-transitory computer-readable media of claim 12 , 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; said 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.

15. The one or more non-transitory computer-readable media of claim 14 , wherein said 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.

16. The one or more non-transitory computer-readable media of claim 14 , wherein said 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-type rows, marking a single row of said each partition; and filtering, from the intermediate results set, modified anti-join-type rows that are unmarked.

17. The one or more non-transitory computer-readable media of claim 16 , 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-type indicator value, an inner-join-type indicator value, and an anti-join-type indicator value; the instructions further comprise instructions that, when executed by one or more processors, cause: 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.

18. The one or more non-transitory computer-readable media of claim 14 , wherein the indicator column is an inner-join indicator column.

19. The one or more non-transitory computer-readable media of claim 12 , 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; said 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.

20. The one or more non-transitory computer-readable media of claim 12 , wherein the query further includes a second filter over the include-all table.

21. The one or more non-transitory computer-readable media of claim 12 , wherein the query further includes one or more additional include-all tables.

22. The one or more non-transitory computer-readable media of claim 12 , wherein the query further includes one or more additional include-matching tables.