MQTs

When populating or refreshing an MQT, it does not account for any predicates or expressions from masks or permissions on dependent tables.

When the MQT is used for optimization in a query, the underlying row permissions and column masks are built into the query that uses the MQT. In order for the MQT to be used for optimization, the MQT must include any columns that are used by the masks or permissions.

In the following example, the MQT TOTALSALES cannot be used by any query that includes CreditCardNum because CustID is used by the mask for CreditCardNum but it is not in the select list from the MQT.

     CREATE SCHEMA MY_LIB	
     CREATE TABLE MY_LIB.SALES(CustID INT, 
                               CreditCardNum VARCHAR(12),
                               Amount DEC(6,2))

     CREATE MASK MY_LIB.CCN_MASK ON SALES FOR COLUMN CreditCardNum 
     RETURN 
     CASE 
        WHEN (CustID < 10) THEN CreditCardNum
        ELSE ‘b*******’ || SUBSTR(CreditCardNum, 9, 4)
     END
     ENABLE;

     CREATE TABLE MY_LIB.TOTALSALES
     AS (SELECT CreditCardNum AS SCCN, SUM(Amount) AS SSUM
      FROM SALES
      GROUP BY CreditCardNum)
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER 

     SELECT CreditCardNum, Sum(Amount)
     FROM MY_LIB.SALES
     GROUP BY CreditCardNum