• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2421
  • Last Modified:

Oracle update statement(Block) performance is not good

Hi ,

The below oracle block (To update ) is running long time .Could you suggest me is there any way we can improve the performance?. i appreciate your help on this.

Thank you,
Sumanth

 
####################################################################
Declare

cursor gtsprodcode_cursor is select * from x1
 
Begin

for gtsprodcode_record in gtsprodcode_cursor Loop

update KDD_REVIEW_20060613_blocktest set cstm_5_tx=gtsprodcode_record.cstm_5_tx
where KDD_REVIEW_20060613_blocktest.review_id=gtsprodcode_record.Review_id;

End Loop;

Commit;

End;
/
#####################################################################









0
sumanth_ora
Asked:
sumanth_ora
1 Solution
 
actonwangCommented:
you just need to use this to update records, more efficient:

update KDD_REVIEW_20060613_blocktest  k
set k.cstm_5_tx=(select cstm_5_tx from x1 where x1.review_id = k.review_id)
/
0
 
actonwangCommented:
you might need to try the following if you have some errors:

update KDD_REVIEW_20060613_blocktest  k
set k.cstm_5_tx=(select max(cstm_5_tx) from x1 where x1.review_id = k.review_id)
/
0
 
MohanKNairCommented:
If review_id is PK or Unique Key for KDD_REVIEW_20060613_blocktest table

UPDATE(select a.cstm_5_tx kdd_cstm_5_tx, a.review_id kdd_review_id, b.cstm_5_tx gts_cstm_5_tx, b.Review_id gts_review_id FROM KDD_REVIEW_20060613_blocktest a, x1 b where a.review_id=b.review_id)
SET kdd_cstm_5_tx=gts_cstm_5_tx;
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JankovskyCommented:
merge KDD_REVIEW_20060613_blocktest trg
using x1
on(trg.review_id=x1.Review_id)
when matched then update set
trg.cstm_5_tx=x.cstm_5_tx

(notation for Oracle 10g)
0
 
sumanth_oraAuthor Commented:
Actually select * from x1 is a big querry.Below is the querry.This querry is working fine.Just for 22500 records ,it is taking 3 hrs.I Dont know why?



 select ccc.review_id,
         (case when ccc.val_ftn=1 and ccc.val_mi=1 then 'CRX'
                     when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.fta + ccc.ftb + ccc.ftc + ccc.ftd + ccc.ftj + ccc.ftl + ccc.ftu + ccc.ftw + ccc.ftx + ccc.ftz >1 then 'FT-'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.fta=1 then 'FTA'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftb=1 then 'FTB'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftc=1 then 'FTC'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftd=1 then 'FTD'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftj=1 then 'FTJ'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftl=1 then 'FTL'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftu=1 then 'FTU'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftw=1 then 'FTW'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftx=1 then 'FTX'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.ftz=1 then 'FTZ'
                  when ccc.val_ftn=0 and ccc.val_mi=1 and ccc.cfi + ccc.cgc + ccc.clr + ccc.cwa > 1 then 'C--'
                  when ccc.val_ftn=0 and ccc.val_mi=1 and ccc.cfi=1 then 'CFI'
                  when ccc.val_ftn=0 and ccc.val_mi=1 and ccc.cgc=1 then 'CGC'
                  when ccc.val_ftn=0 and ccc.val_mi=1 and ccc.clr=1 then 'CLR'
                  when ccc.val_ftn=0 and ccc.val_mi=1 and ccc.cwa=1 then 'CWA' else null end) as cstm_5_tx             
from      (select bbb.review_id, max(bbb.val_ftn) as val_ftn, max(bbb.val_mi) as val_mi,
                     max(bbb.fta) as fta, max(bbb.ftb) as ftb, max(bbb.ftc) as ftc, max(bbb.ftd) as ftd,
                     max(bbb.ftj) as ftj, max(bbb.ftl) as ftl, max(bbb.ftu) as ftu, max(bbb.ftw) as ftw,
                     max(bbb.ftx) as ftx, max(bbb.ftz) as ftz, max(bbb.cfi) as cfi, max(bbb.cgc) as cgc,
                     max(bbb.clr) as clr, max(bbb.cwa) as cwa
            from      (select distinct aaa.*
                        from (select kr.REVIEW_ID, wt.SRC_SYS_CD, wt.FO_TRXN_SEQ_ID,'WIRE' as typ,
                                       1 as val_ftn, 0 as val_mi,
                                       case when wt.SRC_SYS_CD='FTA' then 1 else 0 end as FTA,
                                       case when wt.SRC_SYS_CD='FTB' then 1 else 0 end as FTB,
                                       case when wt.SRC_SYS_CD='FTC' then 1 else 0 end as FTC,
                                       case when wt.SRC_SYS_CD='FTD' or wt.SRC_SYS_CD='FSB' then 1 else 0 end as FTD,
                                       case when wt.SRC_SYS_CD='FTJ' then 1 else 0 end as FTJ,
                                       case when wt.SRC_SYS_CD='FTL' then 1 else 0 end as FTL,
                                       case when wt.SRC_SYS_CD='FTU' then 1 else 0 end as FTU,
                                       case when wt.SRC_SYS_CD='FTW' then 1 else 0 end as FTW,
                                       case when wt.SRC_SYS_CD='FTX' then 1 else 0 end as FTX,
                                       case when wt.SRC_SYS_CD='FTZ' then 1 else 0 end as FTZ,
                                       case when wt.SRC_SYS_CD='CFI' then 1 else 0 end as CFI,
                                       case when wt.SRC_SYS_CD='CGC' then 1 else 0 end as CGC,
                                       case when wt.SRC_SYS_CD='CLR' then 1 else 0 end as CLR,
                                       case when wt.SRC_SYS_CD='CWA' then 1 else 0 end as CWA          
                              from mantas.kdd_review kr, mantas.kdd_break kb, mantas.kdd_break_mtchs kbm, mantas.kdd_dataset_base kdb,
                                     business.wire_trxn_arc wt
                              where
                              --kr.CREAT_TS=(select  to_date(VALUE,'mm/dd/yyyy') FROM business.AML_CONSTANT WHERE VARNAME='DUMP_DT_GTS_PP')
                              --and                    -- DATE MUST BE UPDATED TO REFLECT TARGET ALERT RUNS
                                      kr.OWNER_ORG='GTS' and kr.REVIEW_ID=kb.PRNT_BREAK_ID and kb.BREAK_ID=kbm.BREAK_ID and kbm.DATASET_ID=kdb.DATASET_ID and
                                      kdb.BASE_TABLE='WIRE_TRXN' and kbm.KEY_ID=wt.FO_TRXN_SEQ_ID      
                              UNION ALL
                              select kr2.REVIEW_ID, wt2.SRC_SYS_CD, wt2.FO_TRXN_SEQ_ID,'MI  ' as typ,
                                       0 as val_ftn, 1 as val_mi,
                                       case when wt2.SRC_SYS_CD='FTA' then 1 else 0 end as FTA,
                                       case when wt2.SRC_SYS_CD='FTB' then 1 else 0 end as FTB,
                                       case when wt2.SRC_SYS_CD='FTC' then 1 else 0 end as FTC,
                                       case when wt2.SRC_SYS_CD='FTD' or wt2.SRC_SYS_CD='FSB' then 1 else 0 end as FTD,
                                       case when wt2.SRC_SYS_CD='FTJ' then 1 else 0 end as FTJ,
                                       case when wt2.SRC_SYS_CD='FTL' then 1 else 0 end as FTL,
                                       case when wt2.SRC_SYS_CD='FTU' then 1 else 0 end as FTU,
                                       case when wt2.SRC_SYS_CD='FTW' then 1 else 0 end as FTW,
                                       case when wt2.SRC_SYS_CD='FTX' then 1 else 0 end as FTX,
                                       case when wt2.SRC_SYS_CD='FTZ' then 1 else 0 end as FTZ,
                                       case when wt2.SRC_SYS_CD='CFI' then 1 else 0 end as CFI,
                                       case when wt2.SRC_SYS_CD='CGC' then 1 else 0 end as CGC,
                                       case when wt2.SRC_SYS_CD='CLR' then 1 else 0 end as CLR,
                                       case when wt2.SRC_SYS_CD='CWA' then 1 else 0 end as CWA
                              from mantas.kdd_review kr2, mantas.kdd_break kb2, mantas.kdd_break_mtchs kbm2, mantas.kdd_dataset_base kdb2,
                                     business.mi_trxn_arc wt2
                              where
                               --kr2.CREAT_TS=(select  to_date(VALUE,'mm/dd/yyyy')FROM business.AML_CONSTANT WHERE VARNAME='DUMP_DT_GTS_PP')
                              --  and                            -- DATE MUST BE UPDATED TO REFLECT TARGET ALERT RUNS
                                      kr2.OWNER_ORG='GTS' and kr2.REVIEW_ID=kb2.PRNT_BREAK_ID and kb2.BREAK_ID=kbm2.BREAK_ID and kbm2.DATASET_ID=kdb2.DATASET_ID and
                                      kdb2.BASE_TABLE='MI_TRXN' and kbm2.KEY_ID=wt2.FO_TRXN_SEQ_ID      
                               ) aaa       
                        ) bbb      
            group by bbb.review_id              
            ) ccc
order by 1;
0
 
MohanKNairCommented:
Creating a Materialized view instead of a normal view can boost performance. Also create index on selected columns for the Materialized view.

CREATE MATERIALIZED VIEW x1_MVW REFRESH FAST AS select ccc.review_id,
        (case when ccc.val_ftn=1 and ccc.val_mi=1 then 'CRX'
                  when ccc.val_ftn=1 and ccc.val_mi=0 and ccc.fta   .............

create index x1_mvw_idx1 on x1_MVW (review_id);
0
 
actonwangCommented:
One thing to keep in mind: It will still be faster to run your update in one SQL statement. The PL/SQL block you run will incur unnecssary overhead of context switch between PL/SQL and SQL.

0
 
actonwangCommented:
if your query "x" runs too long alone, you might need to look at your query and try to elimiate subqueries if possible.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now