?
Solved

PL/SQL improve performance - decode

Posted on 2014-08-14
6
Medium Priority
?
457 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

0
Comment
Question by:angel7170
6 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 40260634
Are you comparing for the differences between two tables? What is the goal?
0
 

Author Comment

by:angel7170
ID: 40260638
That is correct. It is comparing for the differences on certain columns
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 200 total points
ID: 40260652
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 40260705
"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
 
LVL 35

Accepted Solution

by:
johnsone earned 1600 total points
ID: 40260717
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
 

Author Comment

by:angel7170
ID: 40260824
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question