Performance of MERGE INTO statement
- From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2008 08:22:04 -0400
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:
