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
countries
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
TIA,
Leslie
Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The first software to power the demand data network
phone: 202-261-3549
--
//www.freelists.org/webpage/oracle-l
Other related posts:
