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