Updating millions of records in oracle

You can use the GO batch terminator with a value to have the statement run over and over again as shown below, but you will need to know how many times the batch needs to execute to delete the data.Also, since there is not an index on Due Date this can cause blocking issues while this runs.The CHAR(13) CHAR(10) will write the output to a new line.And finally the GO is used as the batch terminator.

In the previous two scripts we are deleting 5000 rows at a time, but in some cases that wont avoid blocking issues.The problem with this approach is that there is no an index on the Due Date which will cause SQL Server to scan the table to find the data.This will take the transaction longer to complete and could cause blocking issues.You can read more about this in Books Online: SET ROWCOUNT (Transact-SQL).At this point we have seen different options to delete small amounts of data at a time, but we need to figure out a way to accomplish this task without the need to be in front of the computer the entire time. If you perform the delete inside a cursor your transaction log could grow disproportionally, even if your database is using the simple recovery model.

Leave a Reply