Re: Performance of MERGE INTO statement
Date: Wed, 14 May 2008 15:31:02 -0400
I'm not a warehouse expert, but I do know that frequent writes to tables with bitmap indexes on it is not a good idea (once a day is frequent in DW's). It causes the bitmap indexes to grow uncontrolably and become slow. From my understanding it is typical to rebuilt the bitmap indexes after a data load is complete, but when you load data that frequently that may not be an option.
Somebody with more DW experience may chime in here.
On 5/14/08, Hostetter, Jay M <JHostetter_at_decommunications.com> wrote:
> I'm processing millions of call records per
> day. The lowest level of detail in the fact table for the call date is
> "day". So I could update the fact table once a day from a staging
> table. The tricky part is that some calls span more than one day, so I
> can have call records trickle in several days later. This is why I went
> with the merge statement, because I may need to update fact records. Of
> course, now as I think about it, a few duplicate fact records don't seem
> like they would be a problem (although that goes against my instincts
> from an OLTP point of view).