yogi wrote:
> 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
>> 1. Please do not top post. People read from top to bottom. Post
>> your comments at the bottom.
>>
>> And still no version number? Do you really want help or just to
>> waste people's time?
>>
>> 2. The single most important thing you can do to improve the
>> performance of your code is to get rid of the cursor loop. I'm
>> not sure why this couldn't be done with a simple update statement
>> but if not you need to learn how to use BULK COLLECT and FORALL.
>>
>> Demos in Morgan's Library at www.psoug.org.
>>
>> And if you want help again be courteous to those who are trying
>> to help you. You still didn't post a version number. In the future
>> you need to make an effort.
>> --
>> 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
>
> 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.
There is nothing in your cursor that couldn't be converted into
a bulk collect. If you are having a problem with it then compare
what you wrote to my demo labelled: Fast Way 1 which includes a
cursor definition.
--
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
Received on Thu Aug 24 2006 - 13:28:45 CDT
