Leslie A. Cranston

Multi-dimensional clustering: a new data layout scheme in DB2

We describe the design and implementation of a new data layout scheme, called multi-dimensional clustering, in DB2 Universal Database Version 8. Many applications, e.g., OLAP and data warehousing, process a table or tables in a database using a multi-dimensional access paradigm. Currently, most database systems can only support organization of a table using a primary clustering index. Secondary indexes are created to access the tables when the primary key index is not applicable. Unfortunately, secondary indexes perform many random I/O accesses against the table for a simple operation such as a range query. Our work in multi-dimensional clustering addresses this important deficiency in database systems. Multi-Dimensional Clustering is based on the definition of one or more orthogonal clustering attributes (or expressions) of a table. The table is organized physically by associating records with similar values for the dimension attributes in a cluster. We describe novel techniques for maintaining this physical layout efficiently and methods of processing database operations that provide significant performance improvements. We show results from experiments using a star-schema database to validate our claims of performance with minimal overhead.

Efficient query processing for multi-dimensionally clustered tables in DB2

We have introduced a Multi-Dimensional Clustering (MDC) physical layout scheme in DB2 version 8.0 for relational tables. Multi-Dimensional Clustering is based on the definition of one or more orthogonal clustering attributes (or expressions) of a table. The table is organized physically by associating records with similar values for the dimension attributes in a cluster. Each clustering key is allocated one or more blocks of physical storage with the aim of storing the multiple records belonging to the cluster in almost contiguous fashion. Block oriented indexes are created to access these blocks. In this paper, we describe novel techniques for query processing operations that provide significant performance improvements for MDC tables. Current database systems employ a repertoire of access methods including table scans, index scans, index ANDing, and index ORing. We have extended these access methods for efficiently processing the block based MDC tables. One important concept at the core of processing MDC tables is the block oriented access technique. In addition, since MDC tables can include regular record oriented indexes, we employ novel techniques to combine block and record indexes. Block oriented processing is extended to nested loop joins and star joins as well. We show results from experiments using a star-schema database to validate our claims of performance with minimal overhead.

Predicate derivation and monotonicity detection in DB2 UDB

DB2 universal database allows database schema designers to specify generated columns. These generated columns are useful for maintaining rollup hierarchy variables in warehouses (e.g., date, month, quarter). In order for the generated columns to be useful for query processing, queries must automatically make use of such columns when applicable. In particular, query predicates on the original columns should be rewritten to make use of the generated columns. In this paper, we describe two main aspects of this predicate rewriting technique that allows usage of the generated columns for a variety of query predicate types. The first aspect, monotonicity detection, allows for rewrites in the case of range predicates. The second aspect, predicate derivation, is the technique for using generating expressions for query processing. We show the value of this technique for providing significant performance improvement when combined with indexing or multidimensional clustering in DB2.