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@(protected) [mailto:tboss@(protected)]=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.
