Home » SQL & PL/SQL » SQL & PL/SQL » which is better Bulk Insert/Update or Merge Command (Oracle 10g)
which is better Bulk Insert/Update or Merge Command [message #336481] Sun, 27 July 2008 13:55 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hello,
I have a Scenario in ETL process for loading data into datastage area.
We have to extract data from 20 different source tables and load them into the staging table and once loading is done again we have to Update the table.

Here the conditions(join conditions) used for both Insert and Update are the same.
I need your valuable inputs, which will be faster as we have roughly 2 million data to insert and update daily.
a)Bulk Insert/Update
b)Merge command with hints.

Can you people guide me through..

Thanks.
Re: which is better Bulk Insert/Update or Merge Command [message #336482 is a reply to message #336481] Sun, 27 July 2008 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

> which will be faster as we have roughly 2 million data to insert and update daily.
>a)Bulk Insert/Update
>b)Merge command with hints.


What do your benchmarks show?
Why are you not considering external tables?
Re: which is better Bulk Insert/Update or Merge Command [message #336484 is a reply to message #336482] Sun, 27 July 2008 14:38 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
The whole process of insertion and updation should run less than 1.5 hour. Here we are loading data from existing tables in different DB.
Re: which is better Bulk Insert/Update or Merge Command [message #336487 is a reply to message #336481] Sun, 27 July 2008 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>b)Merge command with hints.
What proof do you have that you know better than the built in optimizer?

Using HINTs would require benchmarking alternatives;
when you seem to embrace the Ready, Aim, Fire school of software development.

>Here we are loading data from existing tables in different DB.
Is "different DB" on same system as DB into which data is being loaded?

[Updated on: Sun, 27 July 2008 18:15] by Moderator

Report message to a moderator

Re: which is better Bulk Insert/Update or Merge Command [message #336560 is a reply to message #336487] Mon, 28 July 2008 02:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you already magically know the rows that need to be updated, then INSERT /*+APPEND*/ and a separate UPDATE will probably be better. If you need to perform a join to work out which ones to INSERT and which ones to UPDATE, then a MERGE will probably be better.

There is another method though that is generally faster still.

If your target data is date range partitioned and 95+% of input rows tend to load into a single partition, AND there are a non-trivial number of updates (say, >1% of the load data); then you will often be better off REBUILDING the partition using a combination of the new and existing data, and then using ALTER TABLE EXCHANGE PARTITION (PEX). This is the only scaleable load method if you have Foreign Keys enabled.

Another factor is SQL*Loader. If you have a large proportion of INSERTs, SQL*Loader (direct path) can out-perform INSERT /*+APPEND*/ on indexed tables because it defers index maintenance (whereas INSERT maintains indexes on-the-fly). Depending on volumes, SQL*Load plus a separate UPDATE can out-perform MERGE.

Ross Leishman
Previous Topic: How to Call Multiple Session through Shell scripting
Next Topic: dbms_sql
Goto Forum:
  


Current Time: Fri Dec 02 14:36:35 CST 2016

Total time taken to generate the page: 0.10434 seconds