RE: Design question re: DW data load

Re: Performance of disable/reenable FK constraints, your statement re: performance in the first paragraph of the original message does not seem to apply to our environment. =20

>From last night's load (at a site doing daily, not weekly, loads):

395,185,940 - Total rows for fact table (T_POS_F),   1,943,366 - rows inserted from staging (one day's worth of POS).

Performance of individual steps: (verbatim from the load job's log, with same other info omitted and some names changed). (This is on a not-particularly-powerful Solaris box with 2 processors.)

1/26/2005 10:30	Begin step:  POS insert
1/26/2005 10:30	Begin task:  POS insert
1/26/2005 10:30	Disabled FK constraint: FK_POS_DAY
1/26/2005 10:30	Disabled FK constraint: FK_POS_STORE
1/26/2005 10:30	Disabled FK constraint: FK_POS_ITEM
1/26/2005 10:30	Disabled FK constraint: FK_POS_TRT
1/26/2005 10:30	Disabled FK constraint: FK_POS_RET_COUNTRY
1/26/2005 10:30	Disabled FK constraint: FK_POS_CURRENCY
1/26/2005 10:30	Disabled FK constraint: FK_POS_ERPDCR
1/26/2005 10:30	Disabled FK constraint: FK_POS_PLANO
1/26/2005 10:30	Loading table T_POS_F
1/26/2005 10:33	Inserted 1943366 row(s) from STG_DAILY_POS into T_POS_F
1/26/2005 10:33	Enabled FK constraint: FK_POS_DAY
1/26/2005 10:33	Enabled FK constraint: FK_POS_STORE
1/26/2005 10:33	Enabled FK constraint: FK_POS_ITEM
1/26/2005 10:33	Enabled FK constraint: FK_POS_TRT
1/26/2005 10:33	Enabled FK constraint: FK_POS_RET_COUNTRY
1/26/2005 10:33	Enabled FK constraint: FK_POS_CURRENCY
1/26/2005 10:33	Enabled FK constraint: FK_POS_ERPDCR
1/26/2005 10:33	Enabled FK constraint: FK_POS_PLANO
1/26/2005 10:33	Stored procedure DAILY_POS_INSERT successful
1/26/2005 10:33	Task completed successfully
1/26/2005 10:33	Step completed successfully

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

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