PL/SQL improve performance - decode

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

angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
Are you comparing for the differences between two tables? What is the goal?
0
angel7170Author Commented:
That is correct. It is comparing for the differences on certain columns
0
sventhanCommented:
try this ...

SELECT * FROM (
 SELECT * FROM
 (
 SELECT * FROM db1.schema.tablea
 WHERE EXISTS ( SELECT 1 FROM db2.schema.tableb WHERE db1.schema.tablea.primarykey = db2.schema.tableb.primarykey)
 EXCEPT
 SELECT * FROM db2.schema.tableb
 WHERE EXISTS ( SELECT 1 FROM db1.schema.tablea WHERE db1.schema.tablea.primarykey = db2.schema.tableb.primarykey)
 ) temp1
 UNION
 SELECT * FROM
 (
 SELECT * FROM db2.schema.tableb
 WHERE EXISTS ( SELECT 1 FROM db1.schema.tablea WHERE db1.schema.tablea.primarykey = db2.schema.tableb.primarykey)
 EXCEPT
 SELECT * FROM db1.schema.tablea
 WHERE EXISTS ( SELECT 1 FROM db2.schema.tableb WHERE db1.schema.tablea.primarykey = db2.schema.tableb.primarykey)
 )temp2
 ) temp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
"Is there a better way to write this query so that the performance is low?"

Likely, yes.  Whenever you use nested selects (like: "select ... from select...") if the inner query returns a lot of rows, those rows may need to be written to temporary segments and those segments may need to be written to physical disk.  Also, they will definitely have to be evaluated without the beneifit of any indexes.  Basically, if you want good performance, don't write queries with nested selects that return a lot of rows in the inner select.

Please tell us:
1. What is the business problem you are trying to solve with this query?
2. Are all of the objects (tables or views) that this query references local, or are some of them remote that are accessed via database links?

Also, be aware that the limit on rownum at the outer level of the query doesn't offer much to help performance.  *All* of the rows that meet the criteria at the inner levels have to be evaluated first, then after they are joined, the first 1000 get returned, and the rest get discarded (but that is after Oracle did the work of fetching, evalutating and joining them).
0
johnsoneSenior Oracle DBACommented:
I tried to put this together to remove the nested selects.  I think I got it and I removed a table that wasn't actually being used.

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, 
       dp.prodvty_type_nm, 
       dam.serial_no, 
       da.prodvty_cd, 
       fp.credit_qt, 
       fp.class_qt, 
       dd.actual_dt, 
       dw.wrkr_no, 
       do.org_loc_cd, 
       fp.day_pendency_qt, 
       fp.month_pendency_qt, 
       DFC.basis_combination_cd    cur_basis_combination_cd, 
       DFF.basis_combination_cd    fil_basis_combination_cd, 
       fp.correction_occurrence_qt 
FROM   dq s, 
       fact_productivity FP, 
       dim_productivity DP, 
       dim_appl_mark DAM, 
       dim_wrkr DW, 
       dim_org DO, 
       dim_date DD, 
       dim_action_code DA, 
       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_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' 
       AND DAM.serial_no = S.ser_num 
       AND DA.prodvty_cd = S.ep_tran_cd 
       AND S.ep_actn_ct_dt = dd.actual_dt 
       AND S.ep_exmr_num = dw.wrkr_no 
       AND S.ep_exmr_lo = do.org_loc_cd 
       AND ( 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(CASE 
                          WHEN ( s.credit_qt < 0 
                                  OR s.class_qt < 0 ) THEN -1 
                          ELSE 1 
                        END, 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


The decode is not your problem.  Using the decode in this way removes the need to extra null checks, the decode is basically doing that behind the scenes for you.

I don't think  you are going to get much better performance out of this one, as you still have to essentially scan the entire table, but if there is an index on DP.PRODVTY_CD it may cut down on the number of records processed.  It may not, you would have to look at before and after plans.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.