RE: Performance of MERGE INTO statement
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <JHostetter@xxxxxxxxxxxxxxxxxxxx>, "'Finn Jorgensen'" <finn.oracledba@xxxxxxxxx>, <david@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2008 11:32:00 -0400
Do you have global stats on the table?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hostetter, Jay M
Sent: Wednesday, May 14, 2008 11:19 AM
To: Finn Jorgensen; david@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx]
Sent: Wednesday, May 14, 2008 9:47 AM
To: david@xxxxxxxxxxxxxxxxxx
Cc: Hostetter, Jay M; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
