Re: Merge doesn't do delete

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l