Skip to Main Content
  • Questions
  • maximum row update at a single go in Oracle DB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pritam.

Asked: February 25, 2020 - 2:39 pm UTC

Answered by: Connor McDonald - Last updated: February 26, 2020 - 1:57 am UTC

Category: SQL - Version: 12.2.0.1.0

Viewed 100+ times

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 we 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.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database