Solution to run parallel runs using SQLLDR/SQL Loader

1
votes

I have a situation !

10 Modules take about close to 2 hours each to process that includes loading data from external files, 20 hours of run is unreasonable and they must run sequentially because of the way it is coded. Each module has a same set of scripts but deals with different set of data.

Components :

a) Tables : TempTableA, FinalTableA, TempTableB, FinalTableB; Each of these tables are uniquely represented by a module Key. The module key is defaulted to '-99' b) External File (FileA,FileB) does not have a module key but only has a data. c) The script knows about the module key for that module. d) .ctr file

The code inside each module more or less has following steps :

Truncate Table TempTableA sqlldr $USER/$PASSWRD@$PRD_SID control=ctr/fileA.ctr log=log/fileA.log bad=log/fileA.bad skip=1 rows=10000 silent=FEEDBACK

update table TempTableA set moduleKey = $moduleKey where moduleKey = '-99' insert into FinalTableA as select * from TempTableA;

Now I cant run these modules in parallel because of these temporary tables being truncated.

Is there a better solution ?

I am aware of the external tables , but this isnt about using external tables it is about how I get around with the problem of using shared temporary tables. And this may not about running parallel loads either.

1
parallel-processingsql-loadertemporarystaging

1 Answers

0
votes

You can use SQL*LOADER in parallel and to make a direct path load. So, you need to configure the command with DIRECT=TRUE and PARALLEL=TRUE.

Supress the UPDATE sentence. Replace it with a constant in the CTL file.

I think you can use a hint like /*+ APPEND */ in the last sentence to INSERT data. But, check your requirements to know if the final table could be partitioned. So, the final step wouldn't be an INSERT INTO type sentence but an EXCHANGE PARTITION one. And this is the fastest way lo upload the data to the final table.

Regards from Chile.

Pável