Materialized query tables (MQTs) are a powerful way to improve response time for complex queries, especially queries that might require some of the following operations:
Aggregated data over one or more dimensions
Joins and aggregates data over a group of tables
Data from a commonly accessed subset of data, that is, from a "hot" horizontal or vertical database partition
Repartitioned data from a table, or part of a table, in a partitioned database environment
Knowledge of MQTs is integrated into the SQL and XQuery compiler. In the compiler, the query rewrite phase and the optimizer match queries with MQTs and determine whether to substitute an MQT for a query that accesses the base tables. If an MQT is used, the EXPLAIN facility can provide information about which MQT was selected. In this case, users need access privileges against the base tables, not rerouted MQTs.
Because MQTs behave like regular tables in many ways, the same guidelines for optimizing data access using table space definitions, creating indexes, and issuing RUNSTATS apply to MQTs.
To help you understand the power of MQTs, the following example shows a multidimensional analysis query and how it takes advantage of MQTs.
In this example, assume a database warehouse that contains a set of customers and a set of credit card accounts. The warehouse records the set of transactions that are made with the credit cards. Each transaction contains a set of items that are purchased together. This schema is classified as a multi-star because has two large tables, one containing transaction items and the other identifying the purchase transactions.
Three hierarchical dimensions describe a transaction: product, location, and time. The product hierarchy is stored in two normalized tables representing the product group and the product line. The location hierarchy contains city, state, and country or region information and is represented in a single de-normalized table. The time hierarchy contains day, month, and year information and is encoded in a single date field. The date dimensions are extracted from the date field of the transaction using built-in functions. Other tables in this schema represent account information for customers and customer information.
An MQT is created with the sum and count of sales for each level of the following hierarchies:
Time, composed of year, month, day.
Many queries can be satisfied from this stored aggregate data. The following example shows how to create an MQT that computes sum and count of sales along the product group and line dimensions; along the city, state, and country dimension; and along the time dimension. It also includes several other columns in its GROUP BY clause.
CREATE TABLE dba.PG_SALESSUM AS ( SELECT l.id AS prodline, pg.id AS pgroup, loc.country, loc.state, loc.city, l.name AS linename, pg.name AS pgname, YEAR(pdate) AS year, MONTH(pdate) AS month, t.status, SUM(ti.amount) AS amount, COUNT(*) AS count FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) > 1990 GROUP BY l.id, pg.id, loc.country, loc.state, loc.city, year(pdate), month(pdate), t.status, l.name, pg.name ) DATA INITIALLY DEFERRED REFRESH DEFERRED; REFRESH TABLE dba.PG_SALESSUM;
Queries that can take advantage of such pre-computed sums would include the following:
Sales by month and product group
Total sales for years after 1990
Sales for 1995 or 1996
Sum of sales for a product group or product line
Sum of sales for a specific product group or product line AND for 1995, 1996
Sum of sales for a specific country.
While the precise answer is not included in the MQT for any of these queries, the cost of computing the answer using the MQT could be significantly less than using a large base table, because a portion of the answer is already computed. MQTs can reduce expensive joins, sorts, and aggregation of base data.
The following sample queries would obtain significant performance improvements because they can use the already computed results in the example MQT.
The first example returns the total sales for 1995 and 1996:
SET CURRENT REFRESH AGE=ANY SELECT YEAR(pdate) AS year, SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY year(pdate);
The second example returns the total sales by product group for 1995 and 1996:
SET CURRENT REFRESH AGE=ANY SELECT pg.id AS "PRODUCT GROUP", SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY pg.id;
The larger the base tables are, the larger the improvements in response time can be because the MQT grows more slowly than the base table. MQTs can effectively eliminate overlapping work among queries by doing the computation once when the MQTs are built and refreshed and reusing their content for many queries.
Creating a user-maintained materialized query table
User-maintained materialized query tables (MQTs) are useful for database systems in which tables of summary data already exist. Custom applications that maintain such summary tables are common. Identifying existing summary tables as user-maintained MQTs causes the query optimizer to use the existing summary table to compute result sets for queries against the base tables.
Note: The query optimizer does not use user-maintained MQTs when selecting an access plan for static queries.
If you create a user-maintained materialized query table, the restrictions associated with a system-maintained materialized query table still apply but with the following exceptions:
INSERT, UPDATE, and DELETE operations are allowed on the materialized query table. However, no validity checking is done against the underlying base tables. You are responsible for the correctness of the data.
LOAD, EXPORT, IMPORT, and data replication will work with this type of materialized query table except there is no validity checking.
You are not allowed to use the REFRESH TABLE statement on this type of materialized query table.
You are not allowed to use the SET INTEGRITY ... IMMEDIATE CHECKED statement on this type of materialized query table.
User-maintained materialized query tables must be defined as REFRESH DEFERRED.
To create a materialized query table, you use the CREATE TABLE statement with the AS fullselect clause and the IMMEDIATE or REFRESH DEFERRED options.
When creating a materialized query table, you have the option of specifying whether the system will maintain the materialized query table or the user will maintain the materialized query table. The default is system-maintained, which can be explicitly specified using the MAINTAINED BY SYSTEM clause. User-maintained materialized query tables are specified using the MAINTAINED BY USER clause.
In large database environments, or data warehouse environments, there are often custom applications that maintain and load user-maintained materialized query tables.
Note: For the optimizer to consider a user-maintained MQT, the query optimization level must be set at Level 2, or at a level greater than or equal to 5.