Handling Materialized Views - Automated Physical Database Design and Tuning - page 147

clause, the merged view should not contain any group-by clause either, or
else we would compromise correctness (i.e., we implicitly define the union
of a set of columns and the empty set as the empty set ∗ ). In these situa-
tions, we additionally unfold all original aggregates into base table columns
so that the original aggregates can be computed from the resulting merged
view. We therefore define

( S 1 , T , J 1 , R 1 , Z 1 , G 1 ) ⊕ ( S 2 , T , J 2 , R 2 , Z 2 , G 2 ) as

R 1 R 2 , ( S M , T , J 1 ∩ J 2 , Z 1 ∩ Z 2 , G M ) , where:

• S M is the set of columns obtained in the no group-by case, plus the

grouping columns if they are not the same as the input views. If the
resulting G M =∅ , all aggregates are unfolded into base table columns.

• G M = ( G 1 ∪ G 2 ) ∪ columns added to S M (note that G 1 =∅∨ G 2 =∅⇒

G M =∅ ).
The following example illustrates the ideas in this section:
V 1 = SELECT R.x, SUM(S.z) V 2 = SELECT R.x, R.z
FROM R, S

FROM R, S

WHERE R.x = S.y WHERE R.x = S.y

AND 10 < R.a < 20 AND 15 < R.a < 50

GROUP BY R.x
V 3 = SELECT S.y, SUM(S.z)
FROM R, S
WHERE R.x = S.z
AND 10 < R.a < 25
GROUP BY S.y

V 1 ⊕ V 2 =SELECT R.x, R.a, S.z, R.z V 1 ⊕ V 3 =SELECT R.x, S.y, R.a,

SUM(S.z)

FROM R, S FROM R, S

WHERE R.x = S.y WHERE R.x = S.y

AND 10 < R.a < 50 AND 10 < R.a < 25

GROUP BY R.a, R.x, S.y

Note that in order to recreate the original views in the presence of general
algebraic aggregates, we sometimes need to add additional columns in the
merged view (e.g., SUM(c) and COUNT(*) for an original aggregate AVG(c) ).

8.2.3 View Reduction
In the previous section we generalized index merging as a mechanism to de-
crease the amount of redundancy between a pair of materialized views. The

∗ For tables with unique constraints, we can define the set of grouping columns of a query without
a group-by clause as the set of all columns in the table and thus keep the definition of union
unchanged. However, this is not correct for tables with duplicate values, because a group-by
clause with all columns eliminates duplicate rows and therefore is not equivalent to the query
without the group-by clause.