View Materialisaion Elimination.



IBM's flagship relational database management system

View Materialisaion Elimination.

Postby GOPI84 » Tue Mar 06, 2012 1:51 pm

I have a query that is been involving a view and the underlying view is getting materialized (Table_type= W) as per explain results, below is the query attached,

SELECT A.EP_APPRAISAL_ID, A.SRC_SYS_ID, A.EMPLID,
A.EMPL_RCD, A.PERIOD_BEGIN_DT, A.PERIOD_END_DT,
A.EP_REVIEW_TYPE, A.EP_TEMPLATE_ID,
A.EP_TMPL_EFFDT, A.CREATION_DT, A.EP_EST_STATUS,
A.EP_EST_DUE_DATE, A.EP_NOM_STATUS, A.EP_REVIEW_ID,
A.EP_RATING, A.RATING_MODEL,
A.REVIEW_POINTS, A.EP_CALC_METHOD, A.EP_RTG_OVERRIDE,
A.EP_EMPL_ACK_DT, A.EP_CREATE_DTTM, A.EP_CREATE_OPRID,
B.HSBC_EP_DOC_PHASE1,
B.HSBC_EP_APPR_ID, B.HSBC_EP_DOC_PHASE2
FROM ((PS_EP_APPR A INNER JOIN PS_HSBC_EMPGLEP_VW A1
ON (A.EP_APPRAISAL_ID = A1.EP_APPRAISAL_ID
AND A.SRC_SYS_ID = A1.SRC_SYS_ID
AND A1.ROWSECCLASS = 'DPGLALL' )) LEFT OUTER JOIN
PS_HSBC_EP_DOC_XRF B ON B.EMPLID = A.EMPLID )
WHERE ( A.EMPLID = '43566152' );

Also, the view is PS_HSBC_EMPGLEP_VW which is getting materialized and is created on one another view and a table.Along with that i have checked that,the view definition does not refer to any GROUPBY,DISTINCT,AGGREGATE FUNCTION OR UNION is pretty strange for me to recognize the exact reason for this materialization.
Thanks,
Gopal.
GOPI84
 
Posts: 4
Joined: Mon Mar 22, 2010 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post