Re: Which Is Better?

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 Received on Mon Sep 18 2006 - 05:41:38 CDT