And God help you SQL optimization 9 tables in relational query

select A.ID,A.NAME,count(distinct Z.ID),count(distinct c.id),count(distinct d.id),count(distinct e.id)
from ORG_UNIT A
left join ICM_MTX_PRC1_ORG B on A.ID = B.PARENT_ORG_CODE
and B.state IN ('ACTIVE','UPDPRO','DELPRO') AND B.MTX_LEVEL = 'ZB'
left join ICM_MTX_PRC_ONE Z on Z.ID = B.PRC1_ID
left join ICM_MTX_PRC_TWO C on C.PARENT_ORG_CODE = A.ID AND C.PRC1_ID = Z.ID AND C.STATE IN ('ACTIVE','UPDPRO','DELPRO')
left join ICM_PRC2_RISK Y on Y.PRC2_ID = C.ID AND Y.STATE IN ('ACTIVE','UPDPRO','DELPRO')
left join ICM_MTX_RISK D on D.PARENT_ORG_CODE = A.ID AND D.ID = Y.RISK_ID AND D.STATE IN ('ACTIVE','UPDPRO','DELPRO')
left join ICM_PRC2_CTRL X on C.ID = X.PRC2_ID AND X.STATE IN ('ACTIVE','UPDPRO','DELPRO')
left join ICM_RISK_CTRL W on W.RISK_ID = D.ID AND W.STATE IN ('ACTIVE','UPDPRO','DELPRO')
left join ICM_MTX_CONTROL E on E.PARENT_ORG_CODE = A.ID AND X.CONTROL_ID = E.ID AND W.CONTROL_ID = E.ID AND E.STATE IN ('ACTIVE','UPDPRO','DELPRO')
WHERE A.PARENT_UNIT_ID IS NOT NULL AND DELETED = '0'
group by A.ID,A.NAME

Started by Adeline at November 16, 2016 - 6:55 PM

Try out union together again segmented group by statistics will soon

Posted by Alvin at November 17, 2016 - 7:24 PM