Hi,
I have a query which is executing with in a second if i dont give parameters in it. but when i include parameters, it is taking more than half an hour to display result. I tried by replacing subqueries with views, but the performance is same with both subqueries and views. So please help me on how to execute this query fast.
SELECT
DPRDC.CATE_NAME "LOB",
MPRD.PRODUCT_NAME "Product",
MPOLG.PLAN_GROUP_NAME "Plan Group",
MPLN.PLAN_NAME "Plan",
Limited_SI "Count of Policies",
FAILED_PREM "Sum of total Premium",
Unlimited_SI "Count of Policies (U SI)",--(Unlimited SI)",
PASSED_PREM "Sum of total Premium (U SI)",--(UnlimitedSI)",
FROM
M_POLICY MPOL
JOIN D_PRODUCT_CATEGORY DPRDC
ON DPRDC.CATE_ID=MPOL.PRODUCT_CATE_ID
JOIN M_PRODUCT MPRD
ON MPRD.PRODUCT_ID=MPOL.PRODUCT_ID
JOIN M_INSURED MI
ON MI.POLICY_ID=MPOL.POLICY_ID
JOIN M_PLAN MPLN
ON MPLN.PLAN_ID=MI.PLAN_ID
JOIN M_PLAN_GROUP MPOLG
ON MPOLG.PLAN_GROUP_ID=MI.PLAN_GROUP_ID
LEFT JOIN (SELECT M.PRODUCT_ID AS PRODUCTID,
MI.PLAN_GROUP_ID AS PLANGROUPID,
MI.PLAN_ID AS PLANID,
SUM(PASSED)AS UNLIMITED_SI,
SUM(FAILED) AS LIMITED_SI
FROM M_POLICY_COVER MPC, M_POLICY
M, M_INSURED MI,
(SELECT
F.POLICY_NO POLICYNO,
COUNT(DISTINCT (CASE WHEN F.GROSS_WRITTEN_PREM_TCUR=0.25 THEN POLICY_NO END)) AS PASSED,
CASE WHEN COUNT(DISTINCT (CASE WHEN F.GROSS_WRITTEN_PREM_TCUR=0.25 THEN POLICY_NO END))=0
THEN 1 ELSE 0 END AS FAILED
FROM F_PREM_TRANS F
GROUP BY
F.POLICY_NO)
WHERE
POLICYNO=M.POLICY_NO
AND M.POLICY_ID=MPC.POLICY_ID(+)
AND MI.INSURED_ID=MPC.INSURED_ID
GROUP BY
M.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID
ORDER BY
M.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID
)
ON PLANID=MPLN.PLAN_ID
AND PRODUCTID=MPRD.PRODUCT_ID
AND
PLANGROUPID=MPOLG.PLAN_GROUP_ID
LEFT JOIN (SELECT B.PRODUCT_ID AS PRODUCTID2,
MI.PLAN_GROUP_ID AS PLANGROUPID2,
MI.PLAN_ID AS PLANID2,
SUM(B.GROSS_WRITTEN_PREM_TCUR) AS PASSED_PREM
FROM
(SELECT CASE WHEN PASSED=1 THEN POLICYNO END AS PASSED_POL FROM
(SELECT F.POLICY_NO
POLICYNO,
COUNT(DISTINCT (CASE WHEN F.GROSS_WRITTEN_PREM_TCUR=0.25 THEN POLICY_NO END)) AS PASSED
FROM
F_PREM_TRANS F
GROUP BY
F.POLICY_NO)
) A,F_PREM_TRANS B,M_INSURED MI
WHERE
(B.POLICY_NO=A.PASSED_POL)
AND
MI.INSURED_ID=B.INSURED_HID
GROUP BY
B.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID
ORDER BY
B.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID)
ON
PLANID2=MPLN.PLAN_ID
AND
PLANGROUPID2=MPOLG.PLAN_GROUP_ID
AND
PRODUCTID2=MPRD.PRODUCT_ID
LEFT JOIN (SELECT B.PRODUCT_ID AS PRODUCTID3,
MI.PLAN_GROUP_ID AS PLANGROUPID3,
MI.PLAN_ID AS PLANID3,
SUM(B.GROSS_WRITTEN_PREM_TCUR)AS
FAILED_PREM
FROM
SELECT CASE WHEN FAILED=1 THEN POLICYNO END AS FAILED_POL FROM
(SELECT F.POLICY_NO POLICYNO,
CASE WHEN COUNT(DISTINCT (CASE WHEN F.GROSS_WRITTEN_PREM_TCUR=0.25 THEN POLICY_NO END))=0
THEN 1 ELSE 0 END AS FAILED
FROM
F_PREM_TRANS F
GROUP BY
F.POLICY_NO)
) A,F_PREM_TRANS B,M_INSURED MI
WHERE
(B.POLICY_NO=A.FAILED_POL)
AND
MI.INSURED_ID=B.INSURED_MID
GROUP BY
B.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID
ORDER BY
B.PRODUCT_ID,MI.PLAN_GROUP_ID,
MI.PLAN_ID)
ON
PLANID3=MPLN.PLAN_ID
AND
PLANGROUPID3=MPOLG.PLAN_GROUP_ID
AND
PRODUCTID3=MPRD.PRODUCT_ID
WHERE
DPRDC.CATE_NAME IN &p_lob --'Plus'
AND MPRD.PRODUCT_NAME IN &p_product --'Maid Insurance'
AND MPOLG.PLAN_GROUP_NAME IN &p_plan_group --'Abnormal'
AND MPLN.PLAN_NAME IN &p_plan --'Standard'
GROUP BY
DPRDC.CATE_NAME,
MPRD.PRODUCT_NAME,
MPOLG.PLAN_GROUP_NAME,
MPLN.PLAN_NAME,
MPLN.PLAN_ID,
Unlimited_SI,
Limited_SI,
FAILED_PREM,
PASSED_PREM,
ORDER BY
DPRDC.CATE_NAME,
MPRD.PRODUCT_NAME,
MPOLG.PLAN_GROUP_NAME,
MPLN.PLAN_NAME
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.