Re: Design question re: DW data load

  • From: tboss@xxxxxxxxxxxxxxxxxx
  • To: leslie.tierstein@xxxxxxxxxxxxxxx
  • Date: Wed, 26 Jan 2005 10:43:40 -0500 (EST)

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 cleansing
> Insert data into fact table: Disable FK constraints; INSERT /*+APPEND
> */; Enable FK constraints
> Current installations: Only run one load job or run load jobs serially,
> never more than one simultaneously
> Design question: How can I rewrite/redesign the Insert step above so
> more that loads can run in parallel.
> We've figured out the staging table part.
> However, the disable constraints; INSERT direct pothered-enable
> constraints doesn't work.=20
> Any thoughts on a redesign/enhancement that would allow parallel
> processing while still supporting the direct-path INSERT?=20

Other related posts: