Re: Which Is Better?

pankaj_wolfhunter_at_yahoo.co.in wrote:
> Vladimir M. Zakharychev wrote:
>
> > pankaj_wolfhunter_at_yahoo.co.in wrote:
> > > Vladimir M. Zakharychev wrote:
> > >
> > > > pankaj_wolfhunter_at_yahoo.co.in wrote:
> > > > > Greetings,
> > > > >
> > > > > I have just joined a new project and was given some application code to
> > > > > look into.
> > > > >
> > > > > In code, there is a need to update a table(table A) from a source table
> > > > > (table B)
> > > > > having millions of rows.
> > > > >
> > > > > This update, what I can see in code, can easily be done using Merge
> > > > > statement
> > > > > but the approach they are using is: record type of the destination
> > > > > table (table A),
> > > > > fetching the records from the source table (table B) into a Bulk
> > > > > collect
> > > > > variable and looping through the bulk collect variable to update the
> > > > > destination table using record variable.
> > > > >
> > > > > Just want to ask which approach, performance wise, will be better?
> > > > > I hope I am clear.
> > > > >
> > > > > Db version:
> > > > > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
> > > > > PL/SQL Release 10.2.0.1.0 - Production
> > > > >
> > > >
> > > > MERGE should be more efficient. However, the easiest way to confirm or
> > > > deny this is to test the performance on current approach and MERGE
> > > > approach and measure differences in resource consumption and execution
> > > > times.
> > > >
> > > > Regards,
> > > > Vladimir M. Zakharychev
> > > > N-Networks, makers of Dynamic PSP(tm)
> > > > http://www.dynamicpsp.com
> > >
> > > Thanks for the reply.
> > > When I asked my team here why they were gng for this approach instead
> > > of MERGE, they said they tried with MERGE and it was taking double the
> > > time. The reason they gave was from a DBA perpspective like it was
> > > fetching more than 30 million rows and it requires more "undo segment",
> > > transaction logs to keep it and process each row using MERGE. (Sorry
> > > for not remembring the exact reason they told)
> > > Main question is can it be a case? Can a MERGE be that slow with its
> > > counter part UPDATE? Or can you direct me to some link where atleast
> > > some difference is document is using MERGE over UPDATE?
> > > TIA
> >
> > Hmm... If update within merge and standalone update the same amount of
> > rows with the same data, the amount of redo and undo should also be the
> > same. Add to this PL/SQL processing overhead and the logic suggests
> > that single merge operation should be more efficient. A question: is
> > MERGE really necessary here - do you do any inserts in that loop, too?
> > Or is it update-only process? Did they try to capture execution
> > statistics for both approaches and compare them to see what could
> > possibly slow down the merge?
> >
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
>
> Thanks. They are not doing any inserts here. Its just an update
> process.
> I just did a dry run of the update part outside the procedure logic for
> both ways.
>
> For MERGE : Elapsed: 00:10:40.29
> For Normal Update : Elapsed: 00:06:38.48
>
> I dont see any 2-3 hrs time interval they were talking about.
>
> I'll ask them if they have some execution plan which they captured for
> the same. I dont think they have some cause it was implemented around
> 6 months back. I'll let u know if i get some more info.
>
> Also, my reason for asking them for MERGE is that the entire logic is
> unnecessarily increasing line of code making the whole logic a bit
> difficult to understand and represents whereas the same can be done in
> just a single MERGE statement. Can this be a valid argument to put
> along with other statistics?

Hmm... MERGE ran about 2 times slower in your test. If this difference is consistent then I think you should stay with the faster method even though it looks more complex. However, it's a bit surprising that MERGE was outperformed by a PL/SQL loop, even though the loop uses array processing. Did you capture any other statistics aside from run time (like gets, physical reads, redo size, etc.?)

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Sep 18 2006 - 12:18:00 CDT