Roberta Cochrane

Answering complex SQL queries using automatic summary tables

We investigate the problem of using materialized views to answer SQL queries. We focus on modern decision-support queries, which involve joins, arithmetic operations and other (possibly user-defined) functions, aggregation (often along multiple dimensions), and nested subqueries. Given the complexity of such queries, the vast amounts of data upon which they operate, and the requirement for interactive response times, the use of materialized views (MVs) of similar complexity is often mandatory for acceptable performance. We present a novel algorithm that is able to rewrite a user query so that it will access one or more of the available MVs instead of the base tables. The algorithm extends prior work by addressing the new sources of complexity mentioned above, that is, complex expressions, multidimensional aggregation, and nested subqueries. It does so by relying on a graphical representation of queries and a bottom-up, pair-wise matching of nodes from the query and MV graphs. This approach offers great modularity and extensibility, allowing for the rewriting of a large class of queries.

How to roll a join: asynchronous incremental view maintenance

Incremental refresh of a materialized join view is often less expensive than a full, non-incremental refresh. However, it is still a potentially costly atomic operation. This paper presents an algorithm that performs incremental view maintenance as a series of small, asynchronous steps. The size of each step can be controlled to limit contention between the refresh process and concurrent operations that access the materialized view or the underlying relations. The algorithm supports point-in-time refresh, which allows a materialized view to be refreshed to any time between the last refresh and the present.

Incremental maintenance for non-distributive aggregate functions

Incremental view maintenance is a well-known topic that has been addressed in the literature as well as implemented in database products. Yet, incremental refresh has been studied in depth only for a subset of the aggregate functions. In this paper we propose a general incremental maintenance mechanism that applies to all aggregate functions, including those that are not distributive over all operations. This class of functions is of great interest, and includes MIN/MAX, STDDEV, correlation, regression, XML constructor, and user defined functions. We optimize the maintenance of such views in two ways. First, by only recomputing the set of affected groups. Second, we extend the incremental infrastructure with work areas to support the maintenance of functions that are algebraic. We further optimize computation when multiple dissimilar aggregate functions are computed in the same view, and for special cases such as the maintenance of MIN/MAX, which are incrementally maintainable over insertions. We also address the important problem of incremental maintenance of views containing super-aggregates, including materialized OLAP cubes. We have implemented our algorithm on a prototype version of IBM DB2 UDB, and an experimental evaluation proves the validity of our approach.

DB2 goes hybrid: integratng native XML and XQuery with relational data and SQL

Comprehensive and efficient support for XML data management is a rapidly increasing requirement for database systems. To address this requirement, DB2 Universal DatabaseTM (UDB) now combines relational data management with native XML support. This makes DB2® a truly hybrid database management system with first-class support for both XML and relational data processing as well as the integration of the two. This paper presents the overall architecture and design aspects of native XML support in DB2 UDB and its integration with the relational data-flow engine. We describe the new XML components in DB2 UDB and show how XML processing leverages much of the infrastructure which is used for relational data.

Active Database Features in SQL3

Many commercial relational database systems provide support for active rules, which are generally referred to as triggers. However, these facilities have been developed independently by different vendors, and triggers developed for one system generally do not work with another. This chapter describes how triggers are supported in the SQL3 standard, and in particular how the triggers relate to other features of the standard.

Maintenance of cube automatic summary tables

Materialized views (or Automatic Summary Tables—ASTs) are commonly used to improve the performance of aggregation queries by orders of magnitude. In contrast to regular tables, ASTs are synchronized by the database system. In this paper, we present techniques for maintaining cube ASTs. Our implementation is based on IBM DB2 UDB.

Starburst II: the extender strikes back!

St:irburst is an extensible relational database system prototype dc>clopcd at lll\I’s Alrnadcn Research Center. ‘I”hc goal tlu-oughout its dct-clopmcnt has been to build a complete relational database system kernel, engineered throughout with the infrastructure for generic extensions, rather than specific extensions for a single applicxition area not previously supportable by relational technology. Grrcntly, Starburst consists of over 300,000 Iincs of C (and sotne C+ + ) source code, and cxccutcs an extended form of the SQL D;ita Definition [ anguagc and the SQL SliI, IIC’f, lXSERI, and DELETE commands, including joins on any number of tables. \Ve demonstrate it on IIl\l’s first RISCtcchnology lvorkstatioo, the 1{1 PC, under the AIX ~pcrating s}stcm (based on ~rli~ J’]).