Thanks for the question, Pritam.
Asked: February 25, 2020 - 2:39 pm UTC
Last updated: February 26, 2020 - 1:57 am UTC
Version: 12.2.0.1.0
Viewed 10K+ times! This question is 
You Asked
Hi,
We've near to 6 million records in our database and we want to update one specific column value for all the records.
query will be like this: update table_xx set column_xx = '' where column_xxx = '';
Is there any limitation in Oracle DB? If we run this query will there be any impact in performance?
Regards,
Pritam
and Connor said...
There is practically no limit. Two things to keep in mind:
1) We need to be able to rollback that transaction if its fails, and that is governed by the available undo space in your undo tablespace. But you can make this any size you want.
2) If that update runs for (say) 2 minutes, then all those rows will be locked for that duration. Whether that is an issue is based on your business requirements. You could mitigate this by updating (say) 100,000 rows at a time and committing, but this would typically take *longer* and it is important that your update actually suits this method. For example: update my_table set col = col + 1 cannot trivially be broken into chunks because you need a record of what you have updated and what is yet to be done.
6 million rows is not that big. I've seen updates of hundreds of millions of rows, so you should be fine notwithstanding the above caveats.
Is this answer out of date? If it is, please let us know via a Comment