Checks Made by Query Rewrite

For query rewrite to occur, there are a number of checks that the data must pass. These
checks are:

■ Join Compatibility Check
■ Data Sufficiency Check
■ Grouping Compatibility Check
■ Aggregate Computability Check

Join Compatibility Check
In this check, the joins in a query are compared against the joins in a materialized
view. In general, this comparison results in the classification of joins into three
categories:

■ Common joins that occur in both the query and the materialized view. These joins
form the common subgraph.
■ Delta joins that occur in the query but not in the materialized view. These joins
form the query delta subgraph.
■ Delta joins that occur in the materialized view but not in the query. These joins
form the materialized view delta subgraph.


Data Sufficiency Check
In this check, the optimizer determines if the necessary column data requested by a
query can be obtained from a materialized view. For this, the equivalence of one
column with another is used. For example, if an inner join between table A and table B is based on a join predicate A.X = B.X, then the data in column A.X will equal the
data in column B.X in the result of the join. This data property is used to match
column A.X in a query with column B.X in a materialized view or vice versa. For
example, consider the following query:

SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

This query can be answered with join_sales_time_product_mv even though the
materialized view does not have s.time_id. Instead, it has t.time_id, which,
through a join condition s.time_id=t.time_id, is equivalent to s.time_id. Thus,
the optimizer might select the following rewrite:

SELECT prod_name, time_id, week_ending_day, SUM(amount_sold)
FROM join_sales_time_product_mv
GROUP BY prod_name, time_id, week_ending_day;


Grouping Compatibility Check
This check is required only if both the materialized view and the query contain a
GROUP BY clause. The optimizer first determines if the grouping of data requested by a query is exactly the same as the grouping of data stored in a materialized view. In
other words, the level of grouping is the same in both the query and the materialized
view. If the materialized views groups on all the columns and expressions in the query and also groups on additional columns or expressions, query rewrite can reaggregate the materialized view over the grouping columns and expressions of the query to derive the same result requested by the query.


Aggregate Computability Check
This check is required only if both the query and the materialized view contain
aggregates. Here the optimizer determines if the aggregates requested by a query can
be derived or computed from one or more aggregates stored in a materialized view.
For example, if a query requests AVG(X) and a materialized view contains SUM(X)
and COUNT(X), then AVG(X) can be computed as SUM(X)/COUNT(X).
If the grouping compatibility check determined that the rollup of aggregates stored in a materialized view is required, then the aggregate computability check determines if it is possible to roll up each aggregate requested by the query using aggregates in the materialized view.

Posted in: Query Rewrite