More Oracle Answers
Question Library
Ask a question about Oracle
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Suchitra Joshi
Expertise
I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.
Experience
15+ years of Oracle PL/SQL Development
Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)
Awards and Honors
OCP - SQL and PL/SQL
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000
| | |
| |
You are here: Experts > Computing/Technology > Oracle > Oracle > SQL Tuning
Oracle - SQL Tuning
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
Add to this Answer Ask a Question
|
|
