DSXchange

Basically we are loading from Sybase to Oracle tables, it takes close to 30 mins. for loading 2 million records. It is direct mapping and no validation.

For the first run it will be full referesh but then onwards it would be delta records. Hence we are using a upsert process (Update then insert)

SYB -> TRN -> ORA

Apart from this when we add a column to the existing record and load the overall load time for 2 million records is 1 hour.

Any suggestions on how to bring the load time down please.

Thanks

Ask your oracle dba to drop indexes and turn off logging for the first run. once your run is complete, ask him to recreate the indexes and turn logging back on and perform a reorg. That should speed up your process.

I would do Insert then Update as opposed to Update then insert.
Less scanning.

And actually
We have two seperate jobs Inserts and Updates
We don't use upsert
but if you .do the before post would be the better way

Thanks.
I have now split the load into 2 jobs.. Now the time taken for load is better.

Thank you.