Optimizing the Query

[rashmigaikwad]

Hi All,

I need help in optimizing the query mentioned below:

SELECT

SUM(CASE WHEN PROD_TYP='HBRMC ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_2p5,
SUM(CASE WHEN PROD_TYP='HBNMC ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_2p5,
SUM(CASE WHEN PROD_TYP='HBOS' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_2p5,
SUM(CASE WHEN PROD_TYP='CMRMC ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMRMC_2p5,
SUM(CASE WHEN PROD_TYP='CMNRM ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMNRM_2p5,
SUM(CASE WHEN PROD_TYP='CMOS' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMOS_2p5,
SUM(CASE WHEN PROD_TYP='PRRMC ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRRMC_2p5,
SUM(CASE WHEN PROD_TYP='PRNRM ' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRNRM_2p5,
SUM(CASE WHEN PROD_TYP='PROS' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PROS_2p5,
SUM(CASE WHEN PROD_TYP='HBRMC _OTH' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_OTH_2p5,
SUM(CASE WHEN PROD_TYP='HBNRM _OTH' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNRM_OTH_2p5,
SUM(CASE WHEN PROD_TYP='HBOS_ OTH' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_OTH_2p5,

SUM(CASE WHEN PROD_TYP='HBRMC ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_3,
SUM(CASE WHEN PROD_TYP='HBNMC ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_3,
SUM(CASE WHEN PROD_TYP='HBOS' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_3,
SUM(CASE WHEN PROD_TYP='CMRMC ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMRMC_3,
SUM(CASE WHEN PROD_TYP='CMNRM ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMNRM_3,
SUM(CASE WHEN PROD_TYP='CMOS' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMOS_3,
SUM(CASE WHEN PROD_TYP='PRRMC ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRRMC_3,
SUM(CASE WHEN PROD_TYP='PRNRM ' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRNRM_3,
SUM(CASE WHEN PROD_TYP='PROS' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PROS_3,
SUM(CASE WHEN PROD_TYP='HBRMC _OTH' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_OTH_3,
SUM(CASE WHEN PROD_TYP='HBNRM _OTH' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNRM_OTH_3,
SUM(CASE WHEN PROD_TYP='HBOS_ OTH' AND INC_MULTIPLE >= 2.50 OR INC_MULTIPLE <3.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_OTH_3,

SUM(CASE WHEN PROD_TYP='HBRMC ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_3p5,
SUM(CASE WHEN PROD_TYP='HBNMC ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_3p5,
SUM(CASE WHEN PROD_TYP='HBOS' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_3p5,
SUM(CASE WHEN PROD_TYP='CMRMC ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMRMC_3p5,
SUM(CASE WHEN PROD_TYP='CMNRM ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMNRM_3p5,
SUM(CASE WHEN PROD_TYP='CMOS' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMOS_3p5,
SUM(CASE WHEN PROD_TYP='PRRMC ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRRMC_3p5,
SUM(CASE WHEN PROD_TYP='PRNRM ' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRNRM_3p5,
SUM(CASE WHEN PROD_TYP='PROS' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PROS_3p5,
SUM(CASE WHEN PROD_TYP='HBRMC _OTH' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_OTH_3p5,
SUM(CASE WHEN PROD_TYP='HBNRM _OTH' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNRM_OTH_3p5,
SUM(CASE WHEN PROD_TYP='HBOS_ OTH' AND INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_OTH_3p5,

SUM(CASE WHEN PROD_TYP='HBRMC ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_4,
SUM(CASE WHEN PROD_TYP='HBNMC ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_4,
SUM(CASE WHEN PROD_TYP='HBOS' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_4,
SUM(CASE WHEN PROD_TYP='CMRMC ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMRMC_4,
SUM(CASE WHEN PROD_TYP='CMNRM ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMNRM_4,
SUM(CASE WHEN PROD_TYP='CMOS' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMOS_4,
SUM(CASE WHEN PROD_TYP='PRRMC ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRRMC_4,
SUM(CASE WHEN PROD_TYP='PRNRM ' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRNRM_4,
SUM(CASE WHEN PROD_TYP='PROS' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PROS_4,
SUM(CASE WHEN PROD_TYP='HBRMC _OTH' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_OTH_4,
SUM(CASE WHEN PROD_TYP='HBNRM _OTH' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNRM_OTH_4,
SUM(CASE WHEN PROD_TYP='HBOS_ OTH' AND INC_MULTIPLE >= 3.5 OR INC_MULTIPLE <4.0 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_OTH_4,

SUM(CASE WHEN PROD_TYP='HBRMC ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_4AB,
SUM(CASE WHEN PROD_TYP='HBNMC ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_4AB,
SUM(CASE WHEN PROD_TYP='HBOS' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_4AB,
SUM(CASE WHEN PROD_TYP='CMRMC ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMRMC_4AB,
SUM(CASE WHEN PROD_TYP='CMNRM ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMNRM_4AB,
SUM(CASE WHEN PROD_TYP='CMOS' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) CMOS_4AB,
SUM(CASE WHEN PROD_TYP='PRRMC ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRRMC_4AB,
SUM(CASE WHEN PROD_TYP='PRNRM ' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PRNRM_4AB,
SUM(CASE WHEN PROD_TYP='PROS' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) PROS_4AB,
SUM(CASE WHEN PROD_TYP='HBRMC _OTH' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_OTH_4AB,
SUM(CASE WHEN PROD_TYP='HBNRM _OTH' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNRM_OTH_4AB,
SUM(CASE WHEN PROD_TYP='HBOS_ OTH' AND (INC_MULTIPLE >= 4.0) AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBOS_OTH_4AB

FROM
STAG_FSA_MLAR

---------------------------------------------------------------------------------------------------

I tried using Group By clause to reduce the size of the query but this executes only the 2nd part of the select query and union also takes more time to execute the query:

SELECT INC_MULTIPLE,PR OD_TYP,LTV,SUM( ADV_IN_QTR) AS ADV_IN_QTR
FROM
STAG_FSA_MLAR1
GROUP BY INC_MULTIPLE,PR OD_TYP,LTV
HAVING (INC_MULTIPLE < 2.50) AND LTV <= 0.75 OR LTV IS NULL

UNION

SELECT INC_MULTIPLE,PR OD_TYP,LTV,SUM( ADV_IN_QTR)
FROM
STAG_FSA_MLAR1
GROUP BY INC_MULTIPLE,PR OD_TYP,LTV
HAVING INC_MULTIPLE >= 3.0 OR INC_MULTIPLE <3.5 AND LTV <= 0.75 OR LTV IS NULL.


Any other work around?

Regards,
Rashmi