Materialized view maintenance and integrity constraint checking
Proceedings of the 1996 ACM SIGMOD international conference on Management of data - SIGMOD '96
We investigate the problem of incremental maintenance of an SQL view in the face of database updates, and show that it is possible to reduce the total time cost of view maintenance by materializing (and maintaining) additional views. We formulate the problem of determining the optimal set of additional views to materialize as an optimization problem over the space of possible view sets (which includes the empty set). The optimization problem is harder than query optimization since it has to
... with multiple view sets, updates of multiple relations, and multiple ways of maintaining each view set for each updated relation. We develop a memoing solution for the problem; the solution can be implemented using the expression DAG representation used in rule-based optimizers such as Volcano. We demonstrate that global optimization cannot, in general, be achieved by locally optimizing each materialized subview, because common subexpressions between di erent materialized subviews can allow nonoptimal local plans to be combined into an optimal global plan. We identify conditions on materialized subviews in the expression DAG when local optimization is possible. Finally, we provide a systematic space of heuristics that can be used to e ciently determine a useful set of additional views to materialize. Our results are particularly important for the e cient checking of assertions (complex integrity constraints) in the SQL-92 standard, since the incremental checking of such integrity constraints is known to be essentially equivalent to the view maintenance problem. Example 1.1 (Additional Materialized Views) Consider a corporate database with two relations: Dept (DName, MName, Budget), which gives the manager and budget for each department in the corporation, and Emp (EName, DName, Salary), which gives the department and the salary of each employee in the corporation. The following materialized view ProblemDept is used to determine those departments whose expense (i.e., the sum of the salaries of the employees in the department) exceeds their budget.