Re: Performance of MERGE INTO statement

From: David Aldridge <david_at_david-aldridge.com>
Date: Wed, 14 May 2008 06:25:23 -0700 (PDT)
Message-ID: <627504.57931.qm@web803.biz.mail.mud.yahoo.com>

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_at_decommunications.com> To: oracle-l_at_freelists.org 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 14 2008 - 08:25:23 CDT