Parallel load into db table
2 posts in topic
Flat View  Flat View

Posted By:   Kenneth_Chin
Posted On:   Thursday, May 1, 2003 09:03 AM

How would I go about splitting a 780mb file(CLOB data, 70,000 rows) and do parallel loading into an Oracle db?

My concerns are memory & performance.

I would normally use sql loader, but direct path loading isn't supported for LOB data and conventional path loading takes an estimated 28 hours for this file. I need to load this file on a nightly basis.

Any links or help is appreciated, thanks!

Re: Parallel load into db table

Posted By:   Simon_Ablett  
Posted On:   Thursday, May 1, 2003 10:22 AM

There's no simple solution to your question but in reality multi-threading the process is probably unlikely to provide much of a gain for you as any resource contention issues are likely to lie at the database end. Here are a couple of things to consider:

1. Are the rows in the file all being pumped into the same table in the database?

2. Are there any relational database integrity checks in place that might be affected (i.e. primary and foreign key constraints etc)?

3. Are other applications likely to be trying to access the tables at the same time as your loader?

4. Are you using page, table or row locking on the affected tables?

5. What happens if an error occurs half way through? Do all previous transactions need to be rewound and the process restarted?

6. How big are your log / rollback segments?

...and so on. If you have a DBA I suggest that you might be best getting them involved.


Re: Parallel load into db table

Posted By:   Edward_Harned  
Posted On:   Thursday, May 1, 2003 09:58 AM

My first suggestion would be to hire a consultant to study your situation and make suggestions. You really can't expect great answers without an analysis.

Some major companies split their database into several smaller databases such as customer numbers from 1 to 10,00 in base_1, 10,0001 to 20,000 in base_2, etc. Then use a standard module to front-end all client access so the applications are unaware of the physical structure.

About | Sitemap | Contact