Huge data

sheema · Post by **sheema** » Fri Oct 17, 2008 8:35 am

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

chulett · Post by **chulett** » Fri Oct 17, 2008 8:45 am

Not sure I'd call that "huge".

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

sheema · Post by **sheema** » Fri Oct 17, 2008 8:49 am

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

Thanks

Nagaraj · Post by **Nagaraj** » Fri Oct 17, 2008 9:41 am

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.

chulett · Post by **chulett** » Fri Oct 17, 2008 10:02 am

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.

sheema · Post by **sheema** » Fri Oct 17, 2008 10:25 am

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

Thanks

sheema · Post by **sheema** » Fri Oct 17, 2008 10:26 am

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

Thanks

chulett · Post by **chulett** » Fri Oct 17, 2008 10:41 am

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

sheema · Post by **sheema** » Fri Oct 17, 2008 10:43 am

sorry,the no of updates are more than inserts.

sheema · Post by **sheema** » Mon Oct 20, 2008 8:16 am

can someone shed some light on this.

Thanks

chulett · Post by **chulett** » Mon Oct 20, 2008 8:18 am

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.

sheema · Post by **sheema** » Mon Oct 20, 2008 8:42 am

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