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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Merge doesn't do delete

Re: Merge doesn't do delete

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Sun, 15 Oct 2006 13:11:00 +0200
Message-ID: <411d50f60610150411y3397d913ud688e5123a54ccb8@mail.gmail.com>


If someone wants to:

  1. insert new rows that are not in the destination table,
  2. update those that are there
  3. delete those that are not,

then it's much better/faster to truncate then insert /*+ append*/

rgds

On 10/14/06, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
>
> Yes, but only on the rows that were updated by the merge itself it will do
> a delete. Rows that didn't match the join condition won't be deleted. Check
> the sql reference for more infos
>
> Stefan
>
> On 10/13/06, Ken Naim <kennaim_at_gmail.com> wrote:
> >
> > In 10g it does do deletes.
> > http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org [mailto:
> > oracle-l-bounce_at_freelists.org]
> > On Behalf Of Yavor Ivanov
> > Sent: Friday, October 13, 2006 9:06 AM
> > To: John_J_Cantu_at_raytheon.com
> > Cc: oracle-l_at_freelists.org
> > Subject: Re: Merge doesn't do delete
> >
> > The purpose of the 'Merge' command is not to sync two tables.
> > It's
> > purpose is to "upsert" new data to a table containing old data. Best for
> > ETL
> > in staging area of DWH. Like if you have lots of data in some dimension
> > table, and you need to add the changes from last month.
> > About the best way to keep two tables in sync... Well, it
> > depends on
> > your data volume. If you can afford it, think about "truncate" and then
> > "insert /*+append*/ ...".
> > Also you may take a look of dbms_rectifier_diff.rectify. This is
> >
> > made for replication, but can do the job.
> >
> > Rgds,
> > Yavor
> >
> > On Fri, 13 Oct 2006 16:30:49 +0300, John J Cantu <
> > John_J_Cantu_at_raytheon.com>
> > wrote:
> >
> > > All,
> > >
> > > The 'Merge' command seems to be incomplete since it doesn't delete
> > records
> > > that don't exist. The 'Merge' command only performs an update or
> > insert.
> > > Isn't the purpose of the 'Merge' command to kind of make one table
> > look
> > > like another one? Sure there will be times when only updating and
> > > inserting records is enough to keep the tables insync, but only if
> > records
> > > aren't deleted from the source table. Any comments or suggestions on
> > the
> > > best way to keep two tables in sync within the same database? Will I
> > have
> > > to run two commands, one command will be the Merge to update and
> > insert
> > > and a delete command that queries for keys that don't exist anymore in
> > the
> > > source table?
> > >
> > > Thanks,
> > >
> > > John Cantu
> >
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 15 2006 - 06:11:00 CDT

Original text of this message

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