MQT examples

The following are examples of using MQTs.

Example 1

The first example is a query that returns information about employees whose job is DESIGNER. The original query:

SELECT D.deptname, D.location, E.firstnme, E.lastname, E.salary+E.comm+E.bonus as total_sal
FROM Department D, Employee E
WHERE D.deptno=E.workdept
AND E.job = 'DESIGNER'

Create a table, MQT1, that uses this query:

CREATE TABLE MQT1 
       AS (SELECT D.deptname, D.location, E.firstnme, E.lastname, E.salary, E.comm, E.bonus, E.job
FROM Department D, Employee E
WHERE D.deptno=E.workdept)
DATA INITIALLY IMMEDIATE  REFRESH DEFERRED 
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER

Resulting new query after replacing the specified tables with the MQT.

SELECT M.deptname, M.location, M.firstnme, M.lastname, M.salary+M.comm+M.bonus as total_sal
FROM MQT1 M
WHERE M.job = 'DESIGNER'

In this query, the MQT matches part of the user query. The MQT is placed in the FROM clause and replaces tables DEPARTMENT and EMPLOYEE. Any remaining selection not done by the MQT query (M.job= 'DESIGNER') is done to remove the extra rows. The result expression, M.salary+M.comm+M.bonus, is calculated. JOB must be in the select-list of the MQT so that the additional selection can be performed.

Visual Explain diagram of the query when using the MQT:

Example 2

Get the total salary for all departments that are located in 'NY'. The original query:

SELECT D.deptname, sum(E.salary) 
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.deptno=E.workdept AND D.location = 'NY'
GROUP BY D.deptname

Create a table, MQT2, that uses this query:

CREATE TABLE MQT2 
        AS (SELECT D.deptname, D.location, sum(E.salary) as sum_sal
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.deptno=E.workdept 
GROUP BY D.Deptname, D.location)
DATA INITIALLY IMMEDIATE  REFRESH DEFERRED 
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER

Resulting new query after replacing the specified tables with the MQT:

SELECT M.deptname, sum(M.sum_sal) 
FROM  MQT2 M
WHERE  M.location = 'NY'
GROUP BY M.deptname

Since the MQT could potentially produce more groups than the original query, the final resulting query must group again and SUM the results to return the correct answer. Also, the selection M.location='NY' must be part of the new query.

Visual Explain diagram of the query when using the MQT: