Re: Performance of MERGE INTO statement
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: JHostetter@xxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 May 2008 06:25:23 -0700 (PDT)
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?
----- Original Message ----
From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, May 14, 2008 8:22:04 AM
Subject: Performance of MERGE INTO statement
I'm in the process of replacing an old database and have been fiddling with a
star schema in the new database (version 9.2.0.7 on AIX). This database stores
call detail records. I'll admit that I have very limited datawarehousing
experience. I've created a fact table and dimension tables, along with the FKs
and bitmap indexes. I've been able to get some star transformations to occur
in my queries, so I think I have the tables designed correctly. I did run into
some issues with my queries which I think may be related to bugs (5381446
and/or 4502658). However this database should end up being in 10g, as soon as
we get the new hardware.
My main issue at the moment is with populating the star schema tables. I've
been using the MERGE INTO statement. I load my data into a staging table that
has no indexes. Then I use the MERGE command to move this data into my fact
table (separate insert statements update the dimension tables). I've found
that Oracle wants to do full table scans of the staging table (which is
expected - we're loading all the records anyway), but it also does an FTS on
the fact and dimension tables. I've been able to force the statement to use my
indexes by placing hints for each index into the MERGE command. I've noticed
that Oracle may choose to ignore some of the hints, depending on how much data
is in the table. I've gathered stats on the tables using dbms_stats. I'm
trying to figure out if I've just run into some quirks with 9i (since Merge is
new) or if I'm doing something wrong. Perhaps it is still better to use PL/SQL
to update the star schema instead of
MERGE? I was just curious if it's normal to lace a merge command with hints
in order to tune it.
Thank you for the input,
Jay
Here is the plan that I end up with when not using hints:
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
| Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 269 |
3019 | | |
| 1 | MERGE | CALL_FACTS | | |
| | |
| 2 | VIEW | | | |
| | |
| 3 | NESTED LOOPS OUTER | | 1 | 191 |
3019 | | |
| 4 | VIEW | | 1 | 125 |
1662 | | |
| 5 | SORT GROUP BY | | 1 | 234 |
1662 | | |
|* 6 | HASH JOIN | | 1 | 234 |
1660 | | |
|* 7 | HASH JOIN | | 1 | 218 |
1655 | | |
|* 8 | HASH JOIN | | 1 | 202 |
1650 | | |
|* 9 | HASH JOIN | | 1 | 195 |
1647 | | |
|* 10 | HASH JOIN | | 1 | 182 |
1597 | | |
|* 11 | HASH JOIN | | 1 | 169 |
1547 | | |
| 12 | TABLE ACCESS FULL| TRUNK_DIM | 675 | 36450 |
3 | | |
| 13 | TABLE ACCESS FULL| CALL_STAGING | 563K| 61M|
1367 | | |
| 14 | TABLE ACCESS FULL | NPA_NXX_DIM | 117K| 1488K|
48 | | |
| 15 | TABLE ACCESS FULL | NPA_NXX_DIM | 117K| 1488K|
48 | | |
| 16 | TABLE ACCESS FULL | DISPOSITION_DIM | 49 | 343 |
2 | | |
| 17 | TABLE ACCESS FULL | OCN_DIM | 6073 | 97168 |
4 | | |
| 18 | TABLE ACCESS FULL | OCN_DIM | 6073 | 97168 |
4 | | |
| 19 | PARTITION RANGE ITERATOR | | | |
| KEY | KEY |
|* 20 | TABLE ACCESS FULL | CALL_FACTS | 1 | 66 |
1358 | KEY | KEY |
--------------------------------------------------------------------------------------------------
**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.
Other related posts:
