0

I have to update 62 millions of records in production database. Its a simple update statement.

Its a pretty big table.

This is the total number of records count in that table = 1251797271.

Can I approach bulk collect method for the updating the records?

Please let me know what is the best approach..

update statement looks like this,

UPDATE CASHFLOW_HIST
SET EFF_DT = '03-JAN-2019'
WHERE EFF_DT= '01-JAN-2019'

Note: I'm not looking for this method, create a new table ,then drop the original table and rename the new table to original table instead of updating a table with millions of records.

Shivaram
  • 11
  • 3
  • 2
    What is wrong with doing the single update statement you showed? (Though assuming the `eff_dt` column has data type DATE, you should be using actual dates instead of strings, which will cause implicit conversions based on NLS settings; and are you sure the `eff_dt` values don't have non-midnight times?) – Alex Poole Jul 24 '19 at 09:40
  • Just run the update. I've done a similar task today, updated 5.5 million rows in 45 seconds. I know, our tables aren't the same, our databases and servers are different, but such a simple update should be straightforward. – Littlefoot Jul 24 '19 at 09:41
  • 1
    Better use `DATE` literals: `DATE '2019-01-03'` and `DATE '2019-01-01'` – Kaushik Nayak Jul 24 '19 at 09:56
  • @KaushikNayak Thank you. I will try this one. – Shivaram Jul 24 '19 at 09:59
  • @AlexPoole Since its a big table the update was running for 2 hours and even though it didn't get complete. I think may be the implicit conversion was the problem. – Shivaram Jul 24 '19 at 10:02
  • 1
    monitoring the execution of row updates and tablespace UNDO usage. https://stackoverflow.com/questions/54215043/oracle-11-2-0-1-how-to-identify-the-row-which-is-currently-updated-by-the-up/54251384#54251384 – Dmitry Demin Jul 24 '19 at 11:03

0 Answers0