Incremental recomputation of recursive queries with nested sets and aggregate functions

Abstract

We examine the power of incremental evaluation systems that use an SQL-like language for maintaining recursively-defined views. We show that recursive queries such as transitive closure, and “alternating paths” can be incrementally maintained in a nested relational language, when some auxiliary relations are allowed. In the presence of aggregate functions, even more queries can be maintained, for example, the “same generation” query. In contrast, it is still an open problem whether such queries are maintainable in relational calculus. We then restrict the language so that no nested relations are involved (but we keep the aggregate functions). Such a language captures the capability of most practical relational database systems. We prove that this restriction does not reduce the incremental computational power; that is, any query that can be maintained in a nested language with aggregates, is still maintainable using only fiat relations. We also show that one does not need auxiliary relations of arity more than 2. In particular, this implies that the recursive queries maintainable in the nested language with aggregates, can be also maintained in a practical relational database systems using auxiliary tables of arity at most 2. This is again in sharp contrast to maintenance in relational calculus, which admits a strict arity-based hierarchy.

Part of this work was done when Wong was visiting Bell Labs and when Libkin was visiting Institute of Systems Science.