Optimize query execution with user-maintained materialized query tables

Review the following example to understand how to optimize query execution with user-maintained materialized query tables (MQTs).

MQTs can reduce the need for expensive joins, sorts, and aggregation of base data. Although the precise answer for a query is not included in the MQT, the cost of computing the answer by using an MQT could be significantly less than the cost of using a large base table, because a portion of the answer is already computed.

Example for optimization of multidimensional-analysis queries

Scenario description

Consider 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.

The schema is classified as a multi-star schema, because it has two large tables, one containing transaction items, and the other identifying the purchase transactions. The following hierarchical dimensions describe a transaction:
  • 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, region, or territory information, and is stored in a single denormalized 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.

A user-maintained MQT is created for sales at each level of the following hierarchies:
  • Product
  • Location
  • Time composed of year, month, and day
Example of a user-maintained MQT
You can create user-maintained MQTs to satisfy many queries by storing aggregate data. The following CREATE TABLE statement shows how to create an MQT that computes the sum and count of sales data along the product group and line dimensions; along the city, state, and country, region, or territory dimensions; 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;
The following queries can take advantage of the precomputed values in the dba.pg_salessum MQT:
  • Sales by month and product group
  • Total sales for the years after 1990
  • Sales for 1995 or 1996
  • The sum of sales for a specific product group or product line
  • The sum of sales for a specific product group or product line in 1995 and 1996
  • The sum of sales for a specific country, region, or territory
Example of a query that returns the total sales for 1995 and 1996
The following query obtains significant performance improvements because it uses the aggregated data in the dba.pg_salessum MQT.
   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);
Example of a query returns the total sales by product group for 1995 and 1996
The following query also benefits from using the aggregated data in the dba.pg_salessum MQT.
   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;