Posted: Mon Jul 30, 2012 9:40 pm Post subject: too many tables join
Hi, could someone please help me in tuning the below query, This query took approximately 15 hours Machine resource to complete.
A.BPP_POLICY_ID_NBR AS POLICY
, A.BPP_STATE_CD AS STATE
, A.BPP_COMPANY_CD AS COMPANY
, A.BPP_LINE_CD AS LINE
, P.PCY_POL_STATUS_CD AS STATUS
, P.PCY_EXPIRATION_DT AS EXPIRE_DATE
, A.BPP_EFFECTIVE_DT AS EFFECTIVE_DTE
, R.RPY_REL_POL_ID_NBR AS RELATED_PLCY
, R.RPY_REL_LINE_CD AS RELATED_LINE
, C.BPI_POL_BAL_AMT AS BAL_AMT
, B.POD_CMPRSK_VHL_CNT AS VHL_CNT
FROM POT_POLICY_CONTROL A
, POT_POLICY P
, NWT_REL_POLICY R
, AIT_POLICY_ACCTG C
, NWT_POL_DATA B
P.PCY_POL_STATUS_CD = '2'
AND A.BPP_STATE_CD = 'IL'
AND A.BPP_COMPANY_CD = '010'
AND A.BPP_LINE_CD = '010'
AND P.PCY_MRP_IND = 'P'
AND A.BPP_EFFECTIVE_DT > '2012-06-30'
AND C.BPI_POL_BAL_AMT > 0
AND B.POD_CMPRSK_VHL_CNT = '4'
AND A.BPP_PART_RANGE_NBR BETWEEN '000' AND '999'
AND P.PCY_PART_RANGE_NBR BETWEEN '000' AND '999'
AND R.RPY_PART_RANGE_NBR BETWEEN '000' AND '999'
AND C.BPI_PART_RANGE_NBR BETWEEN '000' AND '999'
AND B.POD_PART_RANGE_NBR BETWEEN '000' AND '999'
AND A.BPP_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND A.BPP_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR
AND A.BPP_PART_RANGE_NBR = R.RPY_PART_RANGE_NBR
AND A.BPP_POLICY_ID_NBR = R.RPY_POLICY_ID_NBR
AND C.BPI_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND C.BPI_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR
AND C.BPI_PART_RANGE_NBR = B.POD_PART_RANGE_NBR
AND C.BPI_POLICY_ID_NBR = B.POD_POLICY_ID_NBR
FROM POT_POLICY X
, NWT_POL_DATA Y
, POT_POLICY_CONTROL Z
, AIT_POLICY_ACCTG W
, NWT_POL_DATA Q
WHERE X.PCY_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND X.PCY_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Y.POD_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Y.POD_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Z.BPP_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Z.BPP_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND W.BPI_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND W.BPI_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Q.POD_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Q.POD_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Z.BPP_STATE_CD = 'IL'
AND Z.BPP_COMPANY_CD = '010'
AND Z.BPP_LINE_CD = '010'
AND Z.BPP_EFFECTIVE_DT > '2012-06-30'
AND W.BPI_POL_BAL_AMT > 0
AND B.POD_CMPRSK_VHL_CNT = '4'
AND X.PCY_POL_STATUS_CD = '2'
AND X.PCY_MRP_IND = 'S')
AND P.PCY_MODIFIED_TS =
FROM POT_POLICY G
WHERE G.PCY_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND G.PCY_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR)
AND C.BPI_MODIFIED_TS =
FROM AIT_POLICY_ACCTG H
WHERE H.BPI_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND H.BPI_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR)
any suggestions would be simply guesses and a waste of time.
since this is production, you need access to production db2
and the ability to create your own plan table or use production region plan table.
either way, your dba's are the route.
if you don't have access to a dba, and you have no co-workers,
you can not finish the assignment.
nothing we can do without a very long back-and-forth conversation
which Dikdude may wish to do. _________________ Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
nothing we can do without a very long back-and-forth conversation which Dikdude may wish to do.
Gee, thanks, but probably not. . .
I'll keep an eye out and if somethng is posted where i believe i might be able to contribute, i will. Probably would not try to set a new record for "topic with most replies". If we aren't provided with "stuff" we can use, no reason to labor on _________________ Have a good one
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum