Design question re: DW data load

  • From: "Leslie Tierstein" <leslie.tierstein@xxxxxxxxxxxxxxx>
  • To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Jan 2005 06:27:44 -0800

Platform: Oracle 9.2 on various Unixes
Fact table to be loaded: range-partitioned by date, list-sub-partitioned
by country
Source files: Contain 1 or more weeks of data, but only one country per
file; volume may vary from a few 100,000 rows to 10-20 million
Data delivery: (simplest case) Any time on Sunday, between 2 AM and 11
PM (We have no control over the exact time of delivery within this
window, or the order in which multiple files are delivered)
Batch window: Load must be finished and batch reports must be generated
by 7 AM Monday
Installations: Simplest is just one country (US); Largest is 12

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

Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The first software to power the demand data network
phone: 202-261-3549


Other related posts: