Re: Which Is Better?

Vladimir M. Zakharychev wrote:

> 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?

>

>


> Regards,

>     Vladimir M. Zakharychev

>     N-Networks, makers of Dynamic PSP(tm)

>     http://www.dynamicpsp.com


Hi. Sorry for this late. I ran the entire application with MERGE and
compare the statistics. It was almost same (with a bit here and there).

When I told the same thing again to my senior, he said he misunderstood my question earlier and told me the statistics about something else. He said if the question is about MERGE and UPDATE, then both are almost the same
and using anything will do. :-)

This means I was just doing a comparison over some "misunderstanding" :-)

Thanks for all the help. I really appreciate. Received on Wed Sep 20 2006 - 04:48:48 CDT