Re: Which Is Better?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 18 Sep 2006 01:11:53 -0700
Message-ID: <1158567113.856489.62410@k70g2000cwa.googlegroups.com>

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 Received on Mon Sep 18 2006 - 03:11:53 CDT