RE: Performance of MERGE INTO statement
Date: Wed, 14 May 2008 11:32:00 -0400
Message-ID: <DE958AD98A0E4228BBA2020A7C252FDE@KenPC>
Do you have global stats on the table?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Hostetter, Jay M
Sent: Wednesday, May 14, 2008 11:19 AM
To: Finn Jorgensen; david_at_david-aldridge.com; oracle-l_at_freelists.org
Subject: RE: Performance of MERGE INTO statement
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).
Thanks,
Jay
-----Original Message-----
From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com]
Sent: Wednesday, May 14, 2008 9:47 AM
To: david_at_david-aldridge.com
Cc: Hostetter, Jay M; oracle-l_at_freelists.org
Subject: Re: Performance of MERGE INTO statement
Jay,
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.
Finn
On 5/14/08, David Aldridge <david_at_david-aldridge.com> wrote:
>
> A merge into a fact table is a little unconventional, in my
experience.
> 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?
>
**DISCLAIMER
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.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 14 2008 - 10:32:00 CDT