Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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