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 looks like this:

Q1: 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's 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 and the result expression, M.salary+M.comm+M.bonus, is calculated. Note that 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 looks like this:

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 may 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:

 

Parent topic:

Materialized query table optimization