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?
>
>
>
> ----- 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
>
>

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