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
DIM_BILL_TO_ID --> DIM_ADDRESS
DIM_SHIP_TO_ID --> DIM_ADDRESS
I create a MQT with the sql query definition as
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
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?