COMMERCIAL SOLUTIONS

The approaches presented in this chapter have rapidly found success in DBMS systems, and today have an important role in the physical optimization of databases. The environment where materialized views offer the greatest advantages are data analysis solutions, where a commercial DBMS is used to support an OLAP application.

We briefly illustrate here the main features of materialized views for the main actors of the DBMS market (Microsoft, Oracle, and Red Brick). We base this analysis on the papers written by researchers working for DBMS vendors and on the analysis of the technical documentation accompanying these systems. As we did previously in the chapter, we are not considering special non-relational solutions, which are based on a completely different physical model, requiring a different set of techniques.

The main DBMS offering by Microsoft is represented by the SQL Server product. This DBMS offers, since version 2000, an Index Tuning Wizard, which is technically illustrated in Agrawal, Chaudhuri, & Narasayya (2000). The tool is based on the consideration of a number of representative queries, extracted from a workload trace registered on a live system or generated by hand. The tool evaluates which combination of materialized views and indexes offer the most support in the computation of the representative queries. The tool is not specific for use in a data warehouse or multidimensional database context, and it arose in the framework of a Microsoft initiative for the construction of tools for the automatic management of database systems, which produced a preliminary version of the tool where only index selection was realized. It uses an approach similar to the one presented in this chapter, except that views are considered together with indexes on the views themselves and base tables as components that can benefit the computation of the cubes, using a richer set of components that can contribute to improve system efficiency. Heuristic techniques are then used, with a configurable degree of precision, to identify the configuration to adopt. The heuristics are based on preliminary exhaustive search on all the configurations with a number k of elements, which is then extended incrementally by a greedy heuristic to identify the best configuration of n terms.

The support that Oracle Server offers for materialized views is presented in Bello et al. (1998). Materialized views may be incrementally updatable (if the view is a join-only view or it computes an aggregate function); if the view is complex and the system is not able to identify an efficient incremental update strategy, the view must be rematerialized from scratch. Materialized views are useful for many uses of Oracle Server, but are able to offer the greatest advantages on a data warehouse solution.

The Vista component of Red Brick Warehouse Server (Bunker et al., 2001) (now owned by IBM, which acquired Informix) is a specialized solution for aggregate computation and management. Red Brick Server is a specialized DBMS for the construction of data warehouse systems, customized for the management of multidimensional database schemas. The Vista component exploits the specialization of the model and is well integrated with many of the services that characterize the server (data versioning, star indexes, etc.).