[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle update statement(Block) performance is not good

Posted on 2006-06-14
8
Medium Priority
?
2,417 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
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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
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 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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

649 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