DB2 MQT not rewriting role playing columns

database-designdb2

If in a MQT, if I have 2 referential constraints to the same table (role playing) , usage of one of the constraints doesnt allow the query to be optimized

Example

FACT_SALES

DIM_BILL_TO_ID –> DIM_ADDRESS

DIM_SHIP_TO_ID –> DIM_ADDRESS

SALES_AMOUNT

DIM_ADDRESS

DIM_ADDRESS_ID

COUNTRY_NAME

I create a MQT with the sql query definition as

MQT 1

SELECT COUNTRY_NAME,
SUM(SALES_AMOUNT)
FROM
FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_BILL_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
COUNTRY_NAME

When a query is fired, automatically query rewrite happens and MQT is used

Lets say my definition was

MQT 2

SELECT a1.COUNTRY_NAME AS BILL_TO_NAME,
a2.COUNTRY_NAM AS SHIP_TO_NAME,
SUM(SALES_AMOUNT)
FROM
FACT_SALES INNER JOIN DIM_ADDRESS a1 ON FACT_SALES.DIM_BILL_TO_ID = a1.DIM_ADDRESS_ID
INNER JOIN DIM_ADDRESS a2 ON FACT_SALES.DIM_SHIP_TO_ID = a2.DIM_ADDRESS_ID

GROUP BY
a1.COUNTRY_NAME,a2.COUNTRY_NAME

and the query that was fired was

SELECT COUNTRY_NAME,
SUM(SALES_AMOUNT)
FROM
FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_SHIP_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
COUNTRY_NAME

The query rewrite doesn't happen… but instead if I used BILL_TO_ID in the query, then query rewrite happens…

Is there any logic where the DB2 optimizer cant understand role playing columns?

Best Answer

You may wish to try creating indexes over the foreign keys.

Related Solutions

Db2 – Isolation Level for a Data Warehouse

For most databases including Data Warehouses Read Committed is a sufficient isolation level. I wouldn't use Read Uncommitted due to the possibility of incorrect answers. On SQL Server we can also add Read Committed Snapshot Isolation. This versions rows and avoids queries being blocked by updates. Don't know if you have something similar on DB2.

Db2 – Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

Unfortunately, as the error description for SQL20058N reason code 10 states, you can't use an outer join with a refresh immediate MQT:

Explanation

...
10
The REFRESH IMMEDIATE option was specified and the FROM clause referenced more than one table.

User Response

10
Create the materialized query table as REFRESH DEFERRED, or use an inner join without the explicit INNER JOIN syntax.

However, if the MQT definition you supply is really this simple (i.e., pre-computing the join), I wonder why you want to create an MQT in the first place? As you said, performance of the query is good, so why wouldn't you just retain the query as written in the application? Alternately, you could encapsulate the join logic in a view.

Although I would generally advise against this, if there is some serious performance issue with the join that can't be resolved with proper indexing and RUNSTATS, you could replicate this immediate update functionality through triggers on the PHONEUSER and PHONECALL tables that insert records into your CALLLIST table:

create trigger trg_PhoneUser
    after insert on PHONEUSER
    referencing new as n
    for each row
        insert into PHONECALL (userid) values (n.userid);

create trigger trg_PhoneCall
    after insert on PHONECALL
    referencing new as n
    for each row
    begin
        delete from PHONECALL where userid = n.userid and hour is null;--
        insert into PHONECALL values (n.userid, n.callid, n.hour);--
    end;

The first trigger is necessary to handle the outer join (i.e. where a user hasn't made any calls yet). The second trigger inserts the calls, and also deletes the records inserted by the first trigger (as they are no longer necessary once a record in PHONECALL exists). You would need to decide how you want to handle deletes in either table (either through triggers or foreign keys with cascading deletes).

Again, I want to stress that this is a possible solution, but I would only use it as a last resort. Databases are designed to perform joins efficiently, but this might require some proper design and tuning. The table structures above should absolutely not require this kind of solution, but I'm working on the assumption that this is not your actual schema. I am only describing this solution because I can imagine a few edge cases where something like this solution might be necessary.

Related Question