RE: Design question re: DW data load
- From: "Leslie Tierstein" <leslie.tierstein@xxxxxxxxxxxxxxx>
- To: <tboss@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 26 Jan 2005 13:56:13 -0800
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@xxxxxxxxxxxxxxxxxx [mailto:tboss@xxxxxxxxxxxxxxxxxx]=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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
