Solved

Oracle update statement(Block) performance is not good

Posted on 2006-06-14
8
2,393 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
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now