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
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@xxxxxxxxx> wrote: > > In 10g it does do deletes. > http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Yavor Ivanov > Sent: Friday, October 13, 2006 9:06 AM > To: John_J_Cantu@xxxxxxxxxxxx > Cc: oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxxxxxx> > 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 > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > >