Re: Merge doesn't do delete
- From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
- To: salem.ghassan@xxxxxxxxx
- Date: Mon, 16 Oct 2006 19:55:35 -0600
Faster to truncate then insert-append? Sometimes. Not always.
I once had a client who maintained a "data warehouse" (they called it that,
but it was really something very different) where they did this nightly.
With almost every table.
Yes, truncate is very fast. Insert-Append is pretty quick too. Well, when
you're inserting 1,000,000 rows, it's quicker than most alternatives.
But in this case, maybe only 1% or 2% (if that!) of the data actually
changed from day to day.
It was actually not too diffucult to take the "fresh" data as an external
table, use one MINUS operation to to filter out unchanged rows, another to
detect deleted rows, and a MERGE to handle the rest.
Two MINUSes, and a MERGE are a lot of work. But in most cases, I found it
was considerably faster than reloading the entire table and completely
rebuilding all of the indexes.
Sadly, the client was not interested in making their data loads go faster,
so I never gathered a lot of statistics on the improvements...
Should a MERGE statement delete rows? No. That's not what its meant to
do. But using PL/SQL and MERGE (with a temporary table and just a little
dynamic SQL) it is not hard to implement a simple package that does exactly
what the original poster was trying to achieve.
On 10/15/06, Ghassan Salem <salem.ghassan@xxxxxxxxx> wrote:
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
.....
--
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs
Other related posts:
