RE: Performance of MERGE INTO statement
Date: Wed, 14 May 2008 11:18:38 -0400
Finn - I'm not sure why the optimizer thinks that there is only one row. I've gathered stats and the NUM_ROWS column shows 3,041,890. There are presently two partitions on this table. USER_TAB_PARTITIONS shows that one has 1,078 rows and the other has 4,089,540 rows (it's partitioned by month and I've been testing it with one month of data).
Dave - I referenced the 9i Datawarehousing Guide (chapter 13) when setting up the merge statement. I assumed that some of the examples were on fact tables, but looking back over it I see that may not necessarily be the case. 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).
From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com] Sent: Wednesday, May 14, 2008 9:47 AM
Cc: Hostetter, Jay M; oracle-l_at_freelists.org Subject: Re: Performance of MERGE INTO statement
It looks like the optimizer thinks there's only 1 row in the fact table :
|* 20 | TABLE ACCESS FULL | CALL_FACTS | 1 | 66 | 1358 | KEY I would think that would be why it chooses a FTS. The joins between the staging table and the dim tables happens before joining to the fact table and since the staging table has no indexes, those joins has to be FTS and hash joins.
On 5/14/08, David Aldridge <david_at_david-aldridge.com> wrote:
> A merge into a fact table is a little unconventional, in my
> Actually, what's unconventional is the update part of the merge, and
> most fact tables are loaded with a direct path insert operation. Can
> you reconsider your design and find a way of not running the update?
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.