Re: Performance improvment -loop update.

DA Morgan wrote:
> yogi wrote:
> > well Dude sorry if find it offensive...i didn't mean that.....
> >
> > but i got a solution for it..i am posting newly written proc
> > here....it's taking only some seconds....
> >
> > thanks a lot ppl...it was ny first time i asked for the help here..and
> > i got nice response.
> >
> >
> > Cursor c1 is
> >
> > Select a.SupplierCommentCode,
> > a.CustomerCommentCode,
> > a.PCCommentCode,
> > b.rowid
> > from Ems_Currentsoexpedite_Tmp a, ems_currentsoexpedite b
> > where upper(trim(a.FPRunIdentifier)) = (Select
> > decode(count(distinct fprunidentifier),1,'EOD','FIRST')
> > From
> > ems_currentsoexpedite_tmp
> > Where
> > upper(fprunidentifier)='EOD'
> > )
> > and (a.SupplierCommentCode is not null or
> > a.CustomerCommentCode is not null or a.PCCommentCode is not null)
> > and a.demandorderid=b.demandorderid
> > and a.partnumber=b.partnumber
> > and a.expediteqty=b.expediteqty
> > and a.detailstoexpedite=b.detailstoexpedite
> > and nvl(a.daterequired, trunc(sysdate))=nvl(b.daterequired,
> > trunc(sysdate))
> > and upper(b.fprunidentifier)='EOD';
> >
> > Begin
> > --
> > For c1_rec in c1 Loop
> > Begin
> > Update ems_currentsoexpedite
> > Set SupplierCommentCode = c1_rec.SupplierCommentCode,
> > CustomerCommentCode = c1_rec.CustomerCommentCode,
> > PCCommentCode = c1_rec.PCCommentCode
> > where rowid=c1_rec.rowid;
> > /* where upper(trim(FPRunIdentifier)) =
> > 'EOD'
> > and trim(ShopOrderNumber) =
> > c1_rec.ShopOrderNumber
> > and trim(nvl(RequestDate,trunc(sysdate))) =
> > nvl(c1_rec.RequestDate,trunc(sysdate))
> > and trim(DemandOrderId) =
> > c1_rec.DemandOrderId
> > and trim(PartNumber) =
> > c1_rec.PartNumber
> > and nvl(promisedate,trunc(sysdate)) =
> > nvl(c1_rec.promisedate,trunc(sysdate))
> > and nvl(trunc(daterequired),trunc(sysdate)) =
> > nvl(c1_rec.daterequired,trunc(sysdate));
> > */
> > Exception
> > when others then
> > NULL;
> > End;
> > End loop;
> >
> > commit;
> > --
> >
> > v_stepid := v_stepid +10;
> > --
> > -- Once the processing is done then delete the records from the temp
> > table
> > --
> > execute immediate 'Truncate table EMS_CURRENTSOEXPEDITE_TMP';
> > --
> > Commit;
> > v_stepid := v_stepid +10;
> > --
> > --
> > End update_emsaftnooncomments;
> >
> >
> > DA Morgan wrote:
> >> yogi wrote:
> >>> Hi all,
> >>>
> >>> Please help me in tuning this query...
> >> You've got to be kidding. I help a lot of people and try to keep
> >> an open mind but your question, as I read it, is offensive.
> >>
> >> You provide no information about the system
> >> No SLA
> >> No Explain Plan
> >> You have a cursor loop which has been repeatedly sited as inefficient
> >> compared with array processing.
> >>
> >> And then you want someone else to do what you are paid for?
> >> And urgently too?
> >>
> >> No! Perhaps I just need some coffee. But if you are not qualified
> >> to do your job then quit.
> >>
> >> Now I'll go get some coffee. ;-)
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan_at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org

>

>

>

>

>

Hi

[Quoted] it's version Oracle 9i....

i tried it with a single update as well as BULK collect and for all..it's not letting me to refrence it's element with (i) suffix to the variables. i am posting my sigle UPDATE statement here...

              Update (SELECT t.SupplierCommentCode newSupplierCommentCode,

                              t.CustomerCommentCode
newCustomerCommentCode,
                              t.PCCommentCode newPCCommentCode,
                              t1.SupplierCommentCode
oldSupplierCommentCode,
                              t1.CustomerCommentCode
oldCustomerCommentCode,
                              t1.PCCommentCode  oldPCCommentCode
                              from Ems_Currentsoexpedite  t1
,Ems_Currentsoexpedite_Tmp t
                              WHERE upper(trim(t1.FPRunIdentifier))
           =   'EOD'
             and    trim(t1.ShopOrderNumber)                       =
t.ShopOrderNumber
             and    trim(nvl(t1.RequestDate,trunc(sysdate)))       =
trim(nvl(t.RequestDate,trunc(sysdate)))
             and    trim(t1.DemandOrderId)                         =
t.DemandOrderId
             and    trim(t1.PartNumber)                            =
t.PartNumber
             and    nvl(t1.promisedate,trunc(sysdate))             =
nvl(t.promisedate,trunc(sysdate))
             and    nvl(trunc(t1.daterequired),trunc(sysdate))     =
nvl(t.daterequired,trunc(sysdate))
             and    upper(trim(t.FPRunIdentifier)) = (Select
decode(count(distinct a.fprunidentifier),1,'EOD','FIRST')
                                                  From
Ems_Currentsoexpedite_Tmp a
                                                  Where
upper(a.fprunidentifier)='EOD')
            and (t.SupplierCommentCode is not null or
t.CustomerCommentCode is not null or  t.PCCommentCode is not null))
              Set   oldSupplierCommentCode  =  newSupplierCommentCode,
                   oldCustomerCommentCode  =  newCustomerCommentCode,
                   oldPCCommentCode       =  newPCCommentCode

but this query gives me error ..ORA-01779 -can not modify a column which maps to a non-key preserved column.

bulk collect also i used.....i didn't work out...

Thanks a lot to you dude for teaching me FORUM rules and your advice...as i am new to this. Received on Thu Aug 24 2006 - 20:19:26 CEST