SQL Update large table

问题:

I have a question. I need to update two large table - t_contact (170 million rows) and t_participants (11 million rows). This tables both have column CUSTOMER_ID. Some of this IDs wrong and I need to update it. Wrong IDs is about 140 thousand. I understand that if I will use UPDATE TABLE it takes a lot of times, but this two tables mustn't be unavailable for a long time. What should I do?

回答1:

If you have the wrong ID's stored some where you should use merge:

MERGE INTO t_contact D
USING (select * from t_wrong_ids) S
ON (D.CUSTOMER_ID = S.NEW_ID)
WHEN MATCHED THEN UPDATE SET D.CUSTOMER_ID = S.OLD_ID

A lot faster then a normal update.

Second table is the same:

MERGE INTO t_participants D
USING (select * from t_wrong_ids) S
ON (D.CUSTOMER_ID = S.NEW_ID)
WHEN MATCHED THEN UPDATE SET D.CUSTOMER_ID = S.OLD_ID

回答2:

Split one of your tables to parts and process them one by one in PL/SQL block. For example, suppose, IDs are consequent, you take t_participants and split it to parts with 1 million rows in each:

begin
  -- 1 and 11 - hardcoded values, 
  -- since your t_participants table has 11 000 000 rows
  for i in 1..11 loop 
    merge t_contact c
    using (select * from t_participants 
           where id between (i - 1) * 1000000 and i * 1000000) p
       on (c.id = p.id)
     when matched then update ...;
  commit;
  end loop;
end;

I took size of a part 1000000 records, but you can choose another size. It will depend on your server performance. Try to update manually 100, 1000, 10000, etc. rows to define which size is most convenient.

回答3:

declare
  i number := 0;
  cursor s1 is SELECT rowid, t.* FROM table_name  t WHERE column_name =x;
begin
  for c1 in s1 loop
      UPDATE table_name SET column_name = y
             where rowid = c1.rowid;
      i := i + 1;              -- Commit after every X records
      if i > 50000 then
         commit;
         i := 0;
      end if;
  end loop;
  commit;
end;
/