RE: Design question re: DW data load

From: Leslie Tierstein <leslie.tierstein_at_visionchain.com>
Date: Wed, 26 Jan 2005 13:56:13 -0800
Message-ID: <4AE2782C2093B34784E67971035A816003CFB585@ehost011-2.exch011.intermedia.net>

Thanks for your input.=20

Our network just went down, otherwise I would get you the info on how long it takes to disable/enable the FKs. It's actually not too bad. I'll get these performance measurements tomorrow and forward them to you.

As for collecting statistics, the load jobs track which partition(s) are updated in each load, and only gather statistics for those partitions. This seems to be working okay, with good performance. We re-analyze the summary tables, but those have an order of magnitude fewer rows than the base fact tables.

I have previously thought about investigating the use of transportable TTs and partition switches to address the load. I'll pursue this.=20

Leslie

-----Original Message-----
From: tboss_at_bossconsulting.com [mailto:tboss_at_bossconsulting.com]=20 Sent: Wednesday, January 26, 2005 10:44 AM To: Leslie Tierstein
Cc: ORACLE-L
Subject: Re: Design question re: DW data load

I know you'd like to continue w/ direct-path inserts into fact tables, but I wonder if you're eventually going to run into major performance issues w/ the disable/reenable of FK constraints. My guess is, once you get into the 100s of millions of rows, just re-enabling your FK constraints will blow out your maintenance window. This is what we've noticed; it takes hours to re-enable/create FK constraints on large tables. To say nothing of getting your statistics up-to-date after all these inserts (no small task to run stats all the time on a billion row table).

(of course, I may be wrong, you're probably already running a billion row table and everything's working smoothly, and Its my installation that's got issues :-).

Anyway; We use a transportable-tablespace/partition switch concept to load data into our fact tables. You can do all the pre-stats gathering, create local indexes, etc on staging tables, partition switch and all is good.
You can do your staging operations in parallel, because the partition switches take just a few seconds.

my 2 cents, boss

> Current code:
> Load source file (direct path sql*loader) into staging table Data=20
> cleansing Insert data into fact table: Disable FK constraints; INSERT=20
> /*+APPEND */; Enable FK constraints

>=20

> Current installations: Only run one load job or run load jobs=20
> serially, never more than one simultaneously

>=20

> Design question: How can I rewrite/redesign the Insert step above so=20
> more that loads can run in parallel.

>=20

> We've figured out the staging table part.

>=20

> However, the disable constraints; INSERT direct pothered-enable=20
> constraints doesn't work.=3D20

>=20

> Any thoughts on a redesign/enhancement that would allow parallel=20
> processing while still supporting the direct-path INSERT?=3D20

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

Received on Wed Jan 26 2005 - 17:01:53 CST