?
Solved

Oracle update statement(Block) performance is not good

Posted on 2006-06-14
8
Medium Priority
?
2,413 Views
Last Modified: 2008-01-09
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
Comment
Question by:sumanth_ora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16908737
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16908742
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
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16909128
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:Jankovsky
ID: 16909309
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
 

Author Comment

by:sumanth_ora
ID: 16911992
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
 
LVL 16

Accepted Solution

by:
MohanKNair earned 2000 total points
ID: 16917380
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16920112
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16920141
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

764 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