Incremental maintenance of materialized views with outerjoins

Queries using outerjoins appear very frequently in traditional applications such as data warehousing. Lately, they have been widely used in newly emerged systems such as Object-Relational Mapping (image) tools, schema integration and information exchange systems, and probabilistic databases. Materialized views using outerjoins are allowed in many database management systems, but without support for their incremental maintenance. In this paper we present the algorithms used in Sybase SQL Anywhere image for the incremental maintenance of materialized views with outerjoins. The algorithms achieve the following improvements over the previous work with respect to the class of materialized outerjoin views which can be incrementally maintained, and with respect to the performance of the view updates: 1. Relax the requirement for the existence of the primary key attributes in the select list of the view to only some of the tables (namely only the tables referenced as a preserved side in an outerjoin predicate). 2. Relax the null-intolerant property requirement for only some predicates used in the view definition (namely, those outerjoin predicates referencing tables which can be null-supplied by another nested outerjoin). 3. The maintenance of outerjoin views is implemented by using exactly one update statement per view for each table referenced in the view. Another main characteristic of the algorithms is that they allow the design and implementation of the incremental maintenance of materialized views with outerjoins to be easily integrated into the SQL Anywhere Optimizer by relying on the normalized join tree representation used for optimizing queries with outerjoins.