Oracle updating chunks big table

12-Sep-2019 15:34

If you and your users can cope with the data being partially updated at any given time then this (probably with a smaller number of rows then 100,000) gives you the advantage of not blocking your application for other uses as you don't have an update lock on the entire table for the length of time it takes to update all the rows.

I've not used postgres much but assuming it behaves similarly to MS SQL Server (in simple recovery mode) using smaller updates inside explicit transactions can greatly reduce the amount of log file growth but is unlikely to significantly affect overall run-time.

If running the update in batches and allowing normal operations to continue on the application then obviously you shouldn't follow the first recommendation to drop indexes at the start & recreate them at the end - those indexes are likely to be essential to reasonable performance of the application. Submitting data in chunks seems as a reasonable idea to me because it allows you to do migration in steps, control redo (and archive) log size, pause and resume update as needed based on server load.

There are couple other things you can try to make the query run faster. When full table scan and is involved and you have enough resources, it can be quite beneficial.

You can also perform more complicated updates in Oracle.

You may wish to update records in one table based on values in another table.

Note, parallel execution is not a silver bullet, and may not efficient in all cases. Also, I'd double check the efficiency of the functions involved.

Would it be possible to rewrite them the way to minimize number of calls ?

Ipod sex chatrooms

Huge Table_Temp Status (Rows Updated, Last Updated On, Pause Process) VALUES (0, GETDATE(), 0); END; -- First check to see if we should run. Key Field2; -- update the current status UPDATE ts SET ts. None are guaranteed to help but are probably worth testing out.

Scenario : 'employee' table has 900 million records.