Materialized Query Tables and the
Death of Denormalization
DB2 V8 offers a very useful new feature
known as Materialized Query Tables (MQTs). Though not
exclusively for data warehousing, MQTs can improve the
elegance and efficiency of DB2-based data warehouses. An
MQT can be thought of as a view that has been
materialized – that is, a view whose data is physically
stored instead of virtually accessed when needed. Each
MQT is defined as a SQL query (similar to a view), but
the MQT actually stores the query results as data.
Subsequent user queries that require the data can use
the MQT data instead of re-accessing it from the base
tables. By materializing complex queries into MQTs and
then accessing the materialized results, the cost of
materialization is borne only once, when the MQT is
So, you might consider using MQTs for your existing complex
queries. But another approach is to consider using MQTs instead of
denormalization. Instead of denormalizing, implement fully-normalized tables and
then build MQTs where you would have denormalized. With MQTs you can get the
best of both worlds: fully normalized tables to ensure data integrity during
modification and MQTs for efficient querying.
But there are potential drawbacks to using MQTs, in terms of data
currency, resource consumption, and administration. First of all, MQTs are not
magic; they need to be refreshed when the data upon which they are based
changes. Therefore, the underlying data should be relatively static.
Additionally, MQTs consume disk storage. If your shop is storage-constrained you
may not be able to create many MQTs. Finally, keep in mind that MQTs need to be
maintained. If data in the underlying base table(s) changes, then the MQT must
periodically be refreshed with that current data.
There are two methods for creating an MQT: you can create it anew
starting from scratch using CREATE TABLE or you can modify an existing table
into an MQT using ALTER TABLE. The first method uses the CREATE TABLE statement
using syntax that has been augmented to look like a view definition. Consider,
CREATE TABLE DEPT_SAL
(DEPT, TOTAL_SALARY, TOTAL_BONUS, TOTAL_COMM,
AS (SELECT WORKDEPT, SUM(SALARY), SUM(BONUS)
GROUP BY WORKDEPT)
DATA INITIALLY DEFERRED REFRESH DEFERRED
MAINTAINED BY SYSTEM DISABLE QUERY
The SELECT statement defines the MQT data. So far, so good - this
statement looks like a CREATE VIEW statement, except we are creating a table.
Following the SELECT statement though, are several parameters that define the
nature of the MQT. There are parameters for deferring data population and to
specify whether the MQT is to be maintained by the system or the user. You can
also specify how DB2 will use the MQT for query optimization. The choice you
make will impact the type of SELECT that can be used by the MQT being defined.
The default option is to ENABLE QUERY OPTIMIZATION.
But there are limits on the type of SELECT statement that can be
used for query optimization. Optionally, you can DISABLE QUERY OPTIMIZATION. Of
course, this means that the MQT cannot be used for query optimization, but it
can be queried directly.
As of DB2 V8, when an MQT is created, data is not initially
populated into the table. Most MQTs will be maintained by the system, and as
such, data will be populated when the REFRESH TABLE statement is executed. If
the MQT is specified as MAINTAINED BY USER though, it can also be refreshed
using the LOAD utility, INSERT, UPDATE and DELETE, as well as by REFRESH TABLE.
MQTs are fascinating because the DB2 optimizer understands them.
Your queries can continue to reference base tables, but DB2 may access an MQT
instead. During access path selection, the optimizer examines your query to
determine whether query cost can be reduced by replacing your table(s) with an
MQT. The process undertaken by the DB2 optimizer to recognize MQTs and then
rewrite the query to use them is called automatic query rewrite (AQR).
EXPLAIN will show whether AQR was invoked to use an MQT. If the
final query plan comes from a rewritten query, the PLAN_TABLE contains the new
access path using the name of the matched MQTs in the TNAME column. Also,
TABLE_TYPE will be set to 'M' indicating an MQT was used.
The optimizer is somewhat aware of the freshness of
system-maintained MQTs. AQR will be used for a system-maintained MQT only if a
REFRESH TABLE has occurred. Of course, the MQT may not be up-to-date, but DB2
knows that the MQT was refreshed at least once.
This brief overview of MQTs should help to show how DB2 V8 can
optimize access to complex data structures. With MQTs, DBAs can create a fully
normalized physical database implementation – and then create “denormalized”
structures using MQTs. This brings the benefit of data integrity because the
database is fully normalized, along with the speed of retrieval using
materialized query tables.