How to use Multiple instance to load a single table

nkln@you · Post by **nkln@you** » Tue Nov 21, 2006 9:49 am

Hi All,
i have to load some 10 million of recs into a table. Right now i am doing with Truncate and Load. but this job is taking 7 Hrs to complete. I switched to Ora Bulk Loader(automatic) Plus IPC but still it takes 3.5 Hrs. i read about multiple instances and now i am wondering whether i can use this to my advantage. My strategy would be to load some 4-5 sequential files using link partitioner stage and then i will have a job which wil load my table from one sequential file. And If i run multiple instancesof this job then the data load might be fast. But the only thing is am i allowed to run multiple instances against a same table(oracle 9i)? If not What are the other alternatives i have.
Thanks in Advance

ArndW · Post by **ArndW** » Tue Nov 21, 2006 10:19 am

There is nothing stopping you running multiple instances of your job (without the truncate being called by each instance). But the bottleneck might be the speed with which Oracle can load the data as a whole and not per process. I am pretty sure that just splitting up your load into separate streams won't speed up things; the jobs will be competing for locks and might even slow each other down depending upon your data sort order. If your Oracle database is partitioned you could ensure that each load stream only hits one partition to see if you get speed benefits.

chulett · Post by **chulett** » Tue Nov 21, 2006 10:23 am

AFAIK, the only way to run multiple bulk loads against one table is if the table is partitioned and each load is against a specific partition. Otherwise...

Each one can also truncate their own target partition as part of the load.

ArndW · Post by **ArndW** » Tue Nov 21, 2006 10:26 am

I missed the bulk load part, was just thinking in terms of a normal load. I think the bulk loader in Oracle might take an exclusive table lock as well, but can't recall exactly.

dsscholar · Post by **dsscholar** » Tue Nov 21, 2006 10:45 am

I think craig's idea is good.. I had implemented similar stuff in my project and it works...

Instead of having a big data file, have multiple flat files based on a key, in my case it was employee_id, partition the table on this key, and then run multiple instances of the same job to load this data in the table, use the key in the where clause.

nkln@you · Post by **nkln@you** » Tue Nov 21, 2006 9:56 pm

When i was thinking about running multiple instance of the job, i wd change my strategy to Insert Rows Without clearing. Sorry if i mislead u all. I will give the truncate command in the Before Job Subroutine. But the probelm is how to partition the incoming records into say 5 flat files? Is there any query like that? And can i load all th flat files in the same job using Link partitioner? If not What are the other alternatives i have?

ArndW · Post by **ArndW** » Wed Nov 22, 2006 5:37 am

You can use the link partitioner to do this in the same job. Make sure that you do specify the job properties, Performance Tab, "Row Buffer -> Interprocess" so that these are all fired off as separate processes at runtime.

nkln@you · Post by **nkln@you** » Thu Nov 23, 2006 12:40 am

Hi ArndW
i am not a premium member. So am not able to view ur reply. But from whatever u i got, i will have a link partitioner where i will be loading 5 seq. files at simultaneously(after enabling inter process row buffer) making few columns as the key so that all the seq. files have different data. Now how to load the table using these 5 sql files. The probelm is i have to run a job using a sequencer. So how do we run the multiple instances of a job from a sequencer?

ray.wurlod · Post by **ray.wurlod** » Thu Nov 23, 2006 9:46 am

Code: Select all

JobX.invocationID1
JobX.invocationID2
JobX.invocationID3
JobX.invocationID4
JobX.invocationID5