We help IT Professionals succeed at work.

PL/SQL improve performance - decode

angel7170
angel7170 asked
on
486 Views
Last Modified: 2014-08-14
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;]

Open in new window

Comment
Watch Question

Commented:
Are you comparing for the differences between two tables? What is the goal?

Author

Commented:
That is correct. It is comparing for the differences on certain columns
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you very much Sventhan, Markgeer and Johnsone!!!

I ran the query using Johnsone's code, it finished it in 3 secs. This is magic to me! Thank you very much

Markgeer, thanks for your information about rownum. I didn't know it scans the full table to get the first 1000 records.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.