Details on the MQT matching algorithm

Example 3

The MQT contains fewer tables than the query:

SELECT D.deptname, p.projname, sum(E.salary) 
FROM DEPARTMENT D, EMPLOYEE E, EMPPROJACT EP,  PROJECT P
WHERE D.deptno=E.workdept AND E.Empno=ep.empno 
AND ep.projno=p.projno
GROUP BY D.DEPTNAME, p.projname

Create an MQT based on the preceding query:

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

The rewritten query:

SELECT M.deptname, p.projname, SUM(M.sum_sal) 
FROM MQT3 M, EMPPROJACT EP,  PROJECT P
WHERE M.Empno=ep.empno AND ep.projno=p.projno
GROUP BY M.deptname, p.projname

Example 4

Set the total salary for all departments that are located in 'NY'.

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

Create an MQT based on the preceding query:

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

In this example, the constant 'NY' was replaced by a parameter marker and the MQT also had the local selection of location='NY' applied to it when the MQT was populated. The MQT matching algorithm matches the parameter marker and to the constant 'NY' in the predicate D.Location=?. It verifies that the values of the parameter marker are the same as the constant in the MQT; therefore the MQT can be used.

The MQT matching algorithm also attempts to match where the predicates between the MQT and the query are not the same. For example, if the MQT has a predicate SALARY > 50000, and the query has the predicate SALARY > 70000, the MQT contains the rows necessary to run the query. The MQT is used in the query, but the predicate SALARY > 70000 is left as selection in the query, so SALARY must be a column of the MQT.

Example 5

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 an MQT based on the preceding query:

CREATE TABLE MQT5 
        AS (SELECT D.deptname, E.salary
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.deptno=E.workdept)
DATA INITIALLY IMMEDIATE  REFRESH DEFERRED 
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER

In this example, since D.Location is not a column of the MQT, the user query local selection predicate Location='NY' cannot be determined, so the MQT cannot be used.

Example 6

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

Create an MQT based on the preceding query:

CREATE TABLE MQT6(workdept, sumSalary) 
AS (SELECT workdept, sum(salary)
    FROM EMPLOYEE  
    GROUP BY workdept )
DATA INITIALLY IMMEDIATE REFRESH DEFERRED 
ENABLE QUERY OPTIMIZATION 
MAINTAINED BY USER  

In this example, the SUM(salary) aggregation is pushed down through the join to the EMPLOYEE table, allowing for a match and substitution of MQT6. A regrouping to (sum(sum(salary))) is defined at the top of the query to compensate for the grouping pushdown.

Instead of department joining to all the rows in the employee table, it now has the opportunity to join to the predetermined aggregates in MQT6. This type of MQT substitution can result in significant reduction of processing and IO.