Page 1 of 1

View Materialisaion Elimination.

PostPosted: Tue Mar 06, 2012 1:51 pm
by GOPI84
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.