Re: Insert / update in big tables

Batch is coded using proprietary app (PeopleSoft Application Engine ). For all concerns it can be considered as PL/SQL(mostly).

I don't understand the word 'SP' please explain. Checks are made this way (for now) :
UPDATE TEMP_TABLE SET error_flag = 'Y' / ID = (SELECT ID FROM TABLE) WHERE (NOT) EXISTS (CHECK QUERY ) The 10 lines correspond to the 10 possible values for usage. They are hard coded for now. I propose to join the translation table containing all the values for usage instead, using case to decide which field to use. The file is not loaded in direct path but the trace file I've seen indicates I wouldn't win that much time(and I would have to generate the internal ids another way they are now)

The temp table look like this
EXT_ID1,ext_id2,UPD_FLG,product_line_id(generated),fields.., field_usage_1,field_usage_2...field_usage_10.

The indexes are all here there is no problem. IO throughput doesn't seem to be the problem either(disks are reported 5% busy). Since we are in init phase there aren't much updates nearly only inserts and they last too long(45 min for a 10 000 lines file/total time :48 minutes). Received on Wed Mar 22 2006 - 05:20:22 CST