troubleshooting Question

PL/SQL improve performance - decode

Avatar of angel7170
angel7170Flag for United States of America asked on
Oracle Database
6 Comments3 Solutions490 ViewsLast Modified:
Hello,

I have a PL/SQL query written as below. I am using Toad to run this query.
This query basically compares two tables (have about millions of rows) and does a select of certain fields from the tables. The problem is this query runs for hours to return results and sometimes it fails with tablespace error. I am not sure using the decode function causes the load for the query or it is something else.

Is there a better way to write this query so that the performance is low?

Can someone please assist?
Thank you


[Select rownum ROWNUMBER,'Exists in both with diffs' error_message,'SRC' table_type,'EP' table_name
,S.PRODVTY_TYPE_NM, S.EP_SER_NUM, (S.EP_TRAN_CD), S.CREDIT_QT, S.CLASS_QT, S.EP_ACTN_CT_DT, S.EP_EXMR_NUM, S.EP_EXMR_LO, S.DAYS_PENDENCE, S.MONTH_PENDENCE, S.CUR_BASIS_COMBINATION_CD,S.FIL_BASIS_COMBINATION_CD ,S.CORR_OCCUR_QT
,rownum    ROWNUMBER, 'Exists in both with diffs' error_message, 'TRG' table_type, 'PROD' table_name
,T.PRODVTY_TYPE_NM, T.SERIAL_NO,T.PRODVTY_CD, T.CREDIT_QT, T.CLASS_QT,ACTUAL_DT, T.WRKR_NO, T.ORG_LOC_CD, T.DAY_PENDENCY_QT, T.MONTH_PENDENCY_QT,T.CUR_BASIS_COMBINATION_CD, T.FIL_BASIS_COMBINATION_CD , T.CORRECTION_OCCURRENCE_QT
 FROM
  (SELECT 
    PRODVTY_TYPE_NM, SER_NUM, CREDIT_QT, CLASS_QT,
    EP_TRAN_IND,
    EP_TRAN_CD, 
    EP_SUB_TRAN_CD, 
    EP_ACTN_CT_DT, 
    EP_EXMR_NUM,
    EP_ACTN_NUM, 
    EP_EXMR_LO, 
    EP_FY_PP,
    DAYS_PENDENCE,
      MONTH_PENDENCE,
    CUR_BASIS_COMBINATION_CD,
    FIL_BASIS_COMBINATION_CD,
    CASE WHEN (CREDIT_QT < 0 or CLASS_QT < 0) then -1 else  1 END CORR_OCCUR_QT
FROM DQ
 )   S
 JOIN 
 (  SELECT 
 DP.PRODVTY_TYPE_NM,
 DAM.SERIAL_NO,                 -- ep_ser_num
 DW.WRKR_NO,                    -- ep_exmr_num
 DO.ORG_LOC_CD,                 -- ep_exmr_lo
 DD.ACTUAL_DT,                  -- ep_actn_ct_dt
 DA.PRODVTY_CD,                -- ep_tran_cd
 DMA.MILESTONE_NO,             -- ep_actn_num 
-- DA.TRANSACTION_SUB_CD,
 FP.CREDIT_QT ,                 --                 
 FP.DAY_PENDENCY_QT,            
 FP.MONTH_PENDENCY_QT,              
 FP.CORRECTION_OCCURRENCE_QT,   -- always 1
 FP.OCCURRENCE_QT ,             -- always 1
 FP.CLASS_QT ,                  -- 
 FP.FK_DIM_APPL_MARK_ID  ,
 FP.FK_DIM_ACTION_CODE_ID,
 FP.FK_ACTION_DIM_DATE_ID,
 FP.FK_DIM_MILESTONE_ACTION_ID,
 FP.FK_DIM_ACTION_CORRECTION_ID,
 FP.FK_DIM_ORG_ID ,
 FP.FK_DIM_WRKR_ID,
 FP.FK_DIM_LOAD_JOB_ID ,
 FK_DIM_PRODUCTIVITY_ID ,
 DFC.BASIS_COMBINATION_CD CUR_BASIS_COMBINATION_CD,
 DFF.BASIS_COMBINATION_CD FIL_BASIS_COMBINATION_CD
FROM   FACT_PRODUCTIVITY FP
       ,DIM_PRODUCTIVITY DP
       ,DIM_APPL_MARK DAM
       ,DIM_WRKR DW
       ,DIM_ORG DO
       ,DIM_DATE DD 
       ,DIM_ACTION_CODE DA
       , DIM_MILESTONE_ACTION DMA
       ,DIM_FILING_BASIS DFC
       ,DIM_FILING_BASIS DFF
WHERE FP.FK_DIM_PRODUCTIVITY_ID = DP.DIM_PRODUCTIVITY_ID    
  AND FP.FK_DIM_APPL_MARK_ID  = DAM.DIM_APPL_MARK_ID 
  AND FP.FK_DIM_WRKR_ID = DW.DIM_WRKR_ID
  AND FP.FK_DIM_ORG_ID = DO.DIM_ORG_ID
  AND FP.FK_ACTION_DIM_DATE_ID = DD.DIM_DATE_ID
  AND FP.FK_DIM_ACTION_CODE_ID = DA.DIM_ACTION_CODE_ID
  AND FP.FK_DIM_MILESTONE_ACTION_ID = DMA.DIM_MILESTONE_ACTION_ID
  AND FP.FK_CUR_DIM_FILING_BASIS_ID = DFC.DIM_FILING_BASIS_ID
  AND FP.FK_FILE_DIM_FILING_BASIS_ID = DFF.DIM_FILING_BASIS_ID
  AND DP.PRODVTY_CD = 'LIE'
    ) T
     ON  T.SERIAL_NO = S.SER_NUM AND (T.PRODVTY_CD) = (S.EP_TRAN_CD)  AND S.ep_actn_ct_dt = ACTUAL_DT AND S.EP_EXMR_NUM = T.WRKR_NO AND S.EP_EXMR_LO   = T.ORG_LOC_CD  
  WHERE  (
           DECODE(S.CREDIT_QT                 ,T.CREDIT_QT        ,1,0)    = 0
        OR DECODE(S.CLASS_QT                  ,T.CLASS_QT        ,1,0)     = 0
        OR DECODE(S.DAYS_PENDENCE             ,T.DAY_PENDENCY_QT ,1,0)     = 0
        OR DECODE(S.MONTH_PENDENCE            ,T.MONTH_PENDENCY_QT ,1,0)   = 0
        OR DECODE(S.CORR_OCCUR_QT       ,T.CORRECTION_OCCURRENCE_QT ,1,0)     = 0
        OR DECODE(S.CUR_BASIS_COMBINATION_CD  ,T.CUR_BASIS_COMBINATION_CD ,1,0)     = 0
        OR DECODE(S.FIL_BASIS_COMBINATION_CD  ,T.FIL_BASIS_COMBINATION_CD ,1,0)   = 0
       
      )
    AND rownum < 1000;]
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros