Need Help in updating 10 million rows

Question

I need to update the table A with only 2 columns from the table B. (Tables A,B has only 2 columns in common - col1a,co11b ::col2a,col2b

Sample script: I wanted to update all col1a values with col1b where col2a=col2b

UPDATE a
   SET col1a = (SELECT col1b
                  FROM b
                 WHERE a.col2a = b.col2b)

As the data is huge,I want to commit rows at regular intervals (say 5000 records). Can you please suggest .

Answer 1

Since the query you have shown in the question contains a subquery, this is doing a lookup on table b for every row that is being updated. This can be slow, and update is a slow enough operation anyway.

Depending on the design of your tables, I would try to "UPDATE a view" or MERGE. The ability to the UPDATE depends on there being a "key-preserved join". If you don't have that, the UPDATE won't work. It's a little hard to give a proper answer without some sort of DDL for the tables.

UPDATE (
  select col1a, col1b, col2a, col2b
  from 
    a join b on (a.col2a = b.col2b)
)
set col1a = col1b;

If the UPDATE doesn't work, you can then try the MERGE. This should work in more circumstances than the update.

merge into a
using b on (a.col2a = b.col2b)
when matched then update set a.col1a = b.col1b

The advantage of both of these statements is that they work on the whole set of data rather than updating one row at a time. It can be difficult to get an update to perform well.

It might also be worth removing any indexes on the col1a column if there are any.

Answer 2

Thank you all. I got the solution. I was looking for the the solution like below

        CREATE OR REPLACE PROCEDURE xxwv_cc_token_conversion (
   p_source_table   IN   VARCHAR2,
   p_dest_table     IN   VARCHAR2,
   p_column_name    IN   VARCHAR2,
   p_commit_row     IN   NUMBER
)
IS
   TYPE cv_typ IS REF CURSOR;

   t_cur    cv_typ;

   TYPE token_num IS TABLE OF xxwv_ap_token_test2.token_number%TYPE
      INDEX BY BINARY_INTEGER;

   TYPE row_num IS TABLE OF xxwv_ap_token_test2.row_id%TYPE
      INDEX BY BINARY_INTEGER;

   ex_sql   VARCHAR2 (2000);
   t_num    token_num;
   r_num    token_num;
BEGIN
   OPEN t_cur FOR    ' SELECT token_number, row_id FROM '
                  || p_source_table
                  || ' WHERE ROWNUM < 100';

   LOOP
      FETCH t_cur
      BULK COLLECT INTO t_num, r_num LIMIT p_commit_row;

      ex_sql :=
            'UPDATE '
         || p_dest_table
         || ' SET '
         || p_column_name
         || ' = :1,token_flag = ''Y''  WHERE row_id =  :2';
      FORALL i IN 1 .. t_num.COUNT
         EXECUTE IMMEDIATE ex_sql
                     USING t_num (i), r_num (i);
      COMMIT;
      DBMS_OUTPUT.put_line ('commit finished');
      EXIT WHEN t_cur%NOTFOUND;
   END LOOP;

   CLOSE t_cur;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error occured while updating' || SQLERRM);
END xxwv_cc_token_conversion;