Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Which Is Better?

Re: Which Is Better?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 18 Sep 2006 06:14:34 -0700
Message-ID: <1158585274.508776.240530@m7g2000cwm.googlegroups.com>

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 Received on Mon Sep 18 2006 - 08:14:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US