Details on the MQT matching algorithm

 

What follows is a generalized discussion of how the MQT matching algorithm works.

The tables specified in the query and the MQT are examined. If the MQT and the query specify the same tables, then the MQT can potentially be used and matching continues. If the MQT references tables not referenced in the query, then the unreferenced table is examined to determine if it is a parent table in referential integrity constraint. If the foreign key is non-nullable and the two tables are joined using a primary key or foreign key equal predicate, then the MQT can still be potentially used.

 

Example 3

The MQT contains less 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 query above: 

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

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

All predicates specified in the MQT, must also be specified in the query. The query may contain additional predicates. Predicates specified in the MQT must match exactly the predicates in the query. Any additional predicates specified in the query, but not in the MQT must be able to be derived from columns projected from the MQT. See previous example 1.

 

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 query above: 

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 is the same as the constant in the MQT; therefore the MQT can be used.

The MQT matching algorithm will also attempt to match where the predicates between the MQT and the query are not exactly 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 will be 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 query above: 

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.

If the MQT contains grouping, then the query must be a grouping query. The simplest case is where the MQT and the query specify the same list of grouping columns and column functions. In some cases if the MQT specifies a list of group by columns that is a superset of query group by columns, the query can be rewritten to do a step called regrouping. This will reaggreate the groups of the MQT, into the groups required by the query. When regrouping is required, the column functions need to be recomputed. The table below shows the supported regroup expressions.

The regroup new expression/aggregation rules are:

Table 1. Expression/aggregation rules for MQTs
Query MQT Final query
COUNT(*) COUNT(*) as cnt SUM(cnt)
COUNT(*) COUNT(C2) as cnt2 (where c2 is non-nullable) SUM(cnt2)
COUNT(c1) COUNT(c1) as cnt SUM(cnt)
COUNT(C1) (where C1 is non-nullable) COUNT(C2) as cnt2 (where C2 is non-nullable) SUM(cnt2)
COUNT(distinct C1) C1 as group_c1 (where C1 is a grouping column) COUNT(group_C1)
COUNT(distinct C1) where C1 is not a grouping column MQT not usable
COUNT(C2) where C2 is from a table not in the MQT COUNT(*) as cnt cnt*COUNT(C2)
COUNT(distinct C2) where C2 is from a table not in the MQT Not applicable COUNT(distinct C2)
SUM(C1) SUM(C1) as sm SUM(sm)
SUM(C1) C1 as group_c1, COUNT(*) as cnt (where C1 is a grouping column) SUM(group_c1 * cnt)
SUM(C2) where C2 is from a table not in the MQT COUNT(*) as cnt cnt*SUM(C2)
SUM(distinct C1) C1 as group_c1 (where C1 is a grouping column) SUM(group_C1)
SUM(distinct C1) where C1 is not a grouping column MQT not usable
SUM(distinct C2) where C2 is from a table not in the MQT Not applicable SUM(distinct C2)
MAX(C1) MAX(C1) as mx MAX(mx)
MAX(C1) C1 as group_C1 (where C1 is a grouping column) MAX(group_c1)
MAX(C2) where C2 is from a table not in the MQT Not applicable MAX(C2)
MIN(C1) MIN(C1) as mn MIN(mn)
MIN(C1) C1 as group_C1 (where C1 is a grouping column) MIN(group_c1)
MIN(C2) where C2 is from a table not in the MQT Not applicable MIN(C2)

AVG, STDDEV, STDDEV_SAMP, VARIANCE_SAMPand VAR_POP are calculated using combinations of COUNT and SUM. If AVG, STDDEV, or VAR_POP are included in the MQT and regroup requires recalculation of these functions, the MQT cannot be used. IBM recommends that the MQT only use COUNT, SUM, MIN, and MAX. If the query contains AVG, STDDEV, or VAR_POP, it can be recalculated using COUNT and SUM.

If the FETCH FIRST N ROWS clause is specified in the MQT, then a FETCH FIRST N ROWS clause must also be specified in the query and the number of rows specified for the MQT must be greater than or equal to the number of rows specified in the query. It is not recommended that a MQT contain the FETCH FIRST N ROWS clause.

The ORDER BY clause on the MQT can be used to order the data in the MQT if a REFRESH TABLE is run. It is ignored during MQT matching and if the query contains an ORDER BY clause, it will be part of the rewritten query.

 

Parent topic:

Materialized query table optimization

 

Related reference


MQT supported function