Re: Which Is Better?

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 18 Sep 2006 09:08:19 -0700
Message-ID: <1158595697.966459.44180@m7g2000cwm.googlegroups.com>

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? Received on Mon Sep 18 2006 - 11:08:19 CDT