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

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.