Parallel job for loading an oracle table..suggestions needed

[aakashmalu]

Hi,

I am writing a new parallel job for loading an oracle which was already written in server before. In server job it uses Sql Loader to load 300 million flat file in to a oracle table.

My first question is which one is the best approach to load, either to directly use sequential file to load the table or first make a dataset from flat file and then load it to oracle table.

My second question is right now server job using Sql Loader is being loaded in 2 and half hours and my aim is to reduce it atleast by half hour. parallel engine has two nodes.

So please suggest me a good approach which can cut down the load time.

Thanks in Advance
regards
Aakash

[tkbharani]

I dont think you will save any time by converting the sqlldr to OCI stage. Because the time spent in moving the data to Flat file to Dataset must also taken in to count. so some the options are

1. Increase your parallelism to 4 then you can see some change.

We dont your server config . so try by increasing the parallelism.
Totally how many nodes are configured ?

You can also opt for Exchange Partition Technic,in Oracle.

[aakashmalu]

I dont think you will save any time by converting the sqlldr to OCI stage. Because the time spent in moving the data to Flat file to Dataset must also taken in to count. so some the options are

1. Increase your parallelism to 4 then you can see some change.

We dont your server config . so try by increasing the parallelism.
Totally how many nodes are configured ?

You can also opt for Exchange Partition Technic,in Oracle.

Thanks for the reply....

Parallel engine right now is configured with 2 nodes and if i use the SQL LOADER in parallel which is write method, so can i decrease it to half the loading time(because of the 2 nodes)

[chulett]

Maybe... big maybe. If your target table partitioned?

[aakashmalu]

Maybe... big maybe. If your target table partitioned? ...

yes its a partitioned table