3

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?

  • Interesting. I think it has to do with the double join back to the ADDRESS table. Did you run an EXPLAIN over your query to see what it was actually choosing? I'm wondering if the double join back to ADDRESS is causing it to think of that as an expensive table scan? – Chris Aldrich Dec 12 '13 at 22:49
  • 1
    Do you have any indexes over the foreign keys? – Chris Aldrich Dec 12 '13 at 22:51
  • I created the indexes and it worked. Thanks a lot. – Senthil Dec 13 '13 at 18:25
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.