DSXchange

We have a huge table with around 100000000 rows,now we get weekly file,which has to be updated or inserted to this table.What would be the best approach to get this done.
We load the file into a temprary table, should we have a procedure to delete all the rows in the file and again load the rows in a file to the table.
Which method would be performance efficient.

Thanks in advance

Not sure I'd call that "huge".

What database? What's the typical ratio of inserts v. updates, any clue?

Oracle is the database,not sure of the inserts vs updates.

Thanks

For Temp Table Use Full Refresh, Write a small script to truncate the temp tables, and run the full refresh job, configure all this in a sequence.

For Upsert user Upsert Mode in oracle EE stage target.

i think it should be very simple jobs.

Please let me know if you need further information.

Server job, so no "EE" stage.

How big is this weekly file? That size and the ratio of inserts to updates (which is important to know) will drive the design.

The weekly file is around 28 million,the no of updates are more than the updates.(the ratio is around 90:10).


Thanks

The weekly file is around 28 million,the no of updates are more than the updates.(the ratio is around 90:10).


Thanks

"the no of updates are more than the updates".

sorry,the no of updates are more than inserts.

can someone shed some light on this.

Thanks

Your DBA. Have a chat with them and then let us know what approach they recommend and we'll see about helping you implement that in DataStage.

Thank you, I will contact the DBA and get back.