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.

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.