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

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