RE: Merge doesn't do delete
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <Yavor_Ivanov@xxxxxxxx>, <John_J_Cantu@xxxxxxxxxxxx>
- Date: Fri, 13 Oct 2006 09:41:41 -0500
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
Other related posts:
