update very slow

[dnat]

Hi,

I have a job which reads a file and updates a table(oracle) based on the unique key of the table.
The file has 100 million records.
The job has been running for more than 30 hours now and the log still shows Progress 20 percent.
When i checked for some of the rows in the file in the table, the rows have been updated. I am not able to check exactly how many rows have been updated, since the input file is huge.
I didnt mention any commit point in the job.

Even though the file is huge, since i gave the update condition as the unique id in the table, i was expecting it to complete in 4 or 5 hours.

Now that it has crossed more than 30 hours and the update is happening, i am sceptical to kill the job and add the commit point.

Any suggestions?

[ray.wurlod]

Update is always slow because of the extra work it has to do. Is every column in the WHERE clause supported by an index? Is the index being used?

[buzzylee]

If the number of records to be updated in the target table is less then 5-10% of the total table - just create an index on update keys.

If it's higher - create some temporary table (preferably in bulk mode) and then issue MERGE statement (as "after" Stage action).

If you can't predict what percent of table is affected by update go for MERGE approach - Oracle will use better performing update method (full table scan vs index access).

Regards
Buzz

[chulett]

I'd also be curious how many indexes are on the table and if there are foreign keys to other tables, all of which slow things down. Also, how often are you committing? In spite of the fact that you said you didn't "mention any commit point" it must be doing so if you can see your changes in the table data.

[mhester]

I think most stages default to 2000 for the commit - not 100% sure though.

Also, a better design might be to do deletes and inserts instead of updates.