Expert: Suchitra Joshi - 10/27/2009

Question
Hi Suchit,

I need you favor to tune the SQL, See below. The same kind of SQL is running parallely (9 SQL's) with different filter condition TM_PER_ID alone varies in other 8 SQL's 48614-1, 48614-2 till 48614-8. Pls let me know the best way to load the data.

Total no of Records Read: 30458889
Total no of Records inserted: 30458889

Time Taken to Read: 0:52:01 (hh:mm:ss)
Time Taken to Insert: 0:42:22 (hh:mm:ss)

Total Elapsed time to read and Insert : 1:34:23 (hh:mm:ss)

SELECT  /*+ parallel(A) parallel(B) */A.CNTRCT_DSS_ID,
A.TM_PER_ID,
A.HEALTH_PLAN_DSS_ID,
A.MRKT_DSS_ID,
(SELECT /*+ PARALLEL(MCCDSS_CLMS_STATUS)*/CLMS_STATUS_DSS_ID FROM MCCDSS_CLMS_STATUS WHERE UPPER(CLMS_STATUS_DESC)='ALL') CLM_STATUS_DSS_ID,
A.GRPHY_DSS_ID,
A.CHNNL_DSS_ID,
A.ZIP_ALIGN_DSS_ID,
A.TPN_CUST_SEG_DSS_ID,
A.RX_TYPE_DSS_ID,
--CURR
SUM(SUM(A.PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_PROD_RX_CNT,
SUM(SUM(A.PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_PROD_RX_QTY,
SUM(SUM(A.PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_MRKT_RX_CNT,
SUM(SUM(A.PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_MRKT_RX_QTY,
SUM(SUM(A.PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_MRKT_UTIL_DLLR_AMT,
MAX(MAX(A.TPN_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_TPN_PROD_RX_CNT,
MAX(MAX(A.TPN_PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_TPN_PROD_RX_QTY,
MAX(MAX(A.TPN_MRKT_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_TPN_MRKT_RX_CNT,
MAX(MAX(A.TPN_MRKT_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_TPN_MRKT_UNIT_CNT,
SUM(SUM(A.PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_PROD_RX_CNT,
SUM(SUM(A.PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_PROD_UNIT_CNT,
SUM(SUM(A.PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_MRKT_RX_CNT,
SUM(SUM(A.PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_MRKT_UNIT_CNT,
MAX(MAX(A.ENRLLMNT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_ENRLLMNT_CNT,
--PQTR
SUM(SUM(A.PQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_PROD_RX_CNT,
SUM(SUM(A.PQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_PROD_RX_QTY,
SUM(SUM(A.PQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_MRKT_RX_CNT,
SUM(SUM(A.PQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_MRKT_RX_QTY,
SUM(SUM(A.PQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_MRKT_UTIL_DLLR_AMT,
MAX(MAX(A.PQTR_TPN_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_TPN_PROD_RX_CNT,
MAX(MAX(A.PQTR_TPN_PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_TPN_PROD_RX_QTY,
MAX(MAX(A.PQTR_TPN_MRKT_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_TPN_MRKT_RX_CNT,
MAX(MAX(A.PQTR_TPN_MRKT_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_TPN_MRKT_UNIT_CNT,
SUM(SUM(A.PQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_PROD_RX_CNT,
SUM(SUM(A.PQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_PROD_UNIT_CNT,
SUM(SUM(A.PQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_MRKT_RX_CNT,
SUM(SUM(A.PQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_MRKT_UNIT_CNT,
MAX(MAX(A.PQTR_ENRLLMNT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_ENRLLMNT_CNT,
--PYSQTR
SUM(SUM(A.PYSQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_PROD_RX_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_PROD_RX_QTY,
SUM(SUM(A.PYSQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PYSQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.CHNNL_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID) PYSQTR_MRKT_RX_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.CHNNL_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID) PYSQTR_MRKT_RX_QTY,
SUM(SUM(A.PYSQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.CHNNL_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID) PYSQTR_MRKT_UTIL_DLLR_AMT,
MAX(MAX(A.PYSQTR_TPN_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_TPN_PROD_RX_CNT,
MAX(MAX(A.PYSQTR_TPN_PROD_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_TPN_PROD_RX_QTY,
MAX(MAX(A.PYSQTR_TPN_MRKT_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_TPN_MRKT_RX_CNT,
MAX(MAX(A.PYSQTR_TPN_MRKT_UNIT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_TPN_MRKT_UNIT_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_CNTRCT_PROD_RX_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_CNTRCT_PROD_UNIT_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_CNTRCT_MRKT_RX_CNT,
SUM(SUM(A.PYSQTR_PROD_RX_QTY)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_CNTRCT_MRKT_UNIT_CNT,
MAX(MAX(A.PYSQTR_ENRLLMNT_CNT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYSQTR_ENRLLMNT_CNT,
TRUNC(SYSDATE,'DD'),
SUM(SUM(A.PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) CURR_CNTRCT_MRKT_UTIL_DLLR_AMT,
SUM(SUM(A.PQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PQTR_CNTRCT_MRKT_UTIL_DLLR_AMT,
SUM(SUM(A.PYSQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.MRKT_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYS_CNTRCT_PROD_UTIL_DLLR_AMT,
SUM(SUM(A.PYSQTR_PROD_UTIL_DLLR_AMT)) OVER (PARTITION BY A.CNTRCT_DSS_ID,A.TM_PER_ID,B.PROD_GROUP_DSS_ID,A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID) PYS_CNTRCT_MRKT_UTIL_DLLR_AMT
FROM
MCCDSS_QTR_HPFU_STG A,MCCDSS_MRKT B
WHERE A.MRKT_DSS_ID=B.MRKT_DSS_ID
AND A.CNTRCT_DSS_ID=99999996
AND A.TM_PER_ID=48614
GROUP BY A.CNTRCT_DSS_ID,A.TM_PER_ID,A.HEALTH_PLAN_DSS_ID,A.MRKT_DSS_ID,b.PROD_GROUP_DSS_ID,
A.CHNNL_DSS_ID,A.GRPHY_DSS_ID,A.ZIP_ALIGN_DSS_ID,A.TPN_CUST_SEG_DSS_ID,A.RX_TYPE_DSS_ID


Answer
Hi Prabhu,

You can check the following to fine tune this query -

1) Index created on table MCCDSS_QTR_HPFU_STG (MRKT_DSS_ID, CNTRCT_DSS_ID, TM_PER_ID).
2) If table MCCDSS_CLMS_STATUS contains many rows, create index for column CLMS_STATUS_DESC and change the following condition WHERE UPPER(CLMS_STATUS_DESC)='ALL' to WHERE CLMS_STATUS_DESC = 'ALL' or CLMS_STATUS_DESC = 'all'

Apart from these 2 points,  I don't see any other changes necessary.


Regards

Suchitra