Update huge table from other (huge) table in SQL Oracle

0
votes

I have two big tables:

Table1 (from_id, to_id, field1, field2, field3) ~ 500K rows

Table2 (id_num, field1, field2, field3) ~4M rows

I need to update Table2 from Table1 data based on Table2.id_num that should be between Table1.from_id and Table1.to_id.

In both tables - ID value can vary between 16,000,000 and 4,300,000,000.

I ran the following cursor script, but it runs for hours and not finished yet. 

DECLARE
 l_FROM_ID                Table1.FROM_ID%TYPE;
 l_TO_ID                  Table1.TO_ID%TYPE;
 l_Field1                 Table1.Field1%TYPE;
 l_Field2                 Table1.Field1%TYPE;
 l_Field3                 Table1.Field1%TYPE;

CURSOR cur
     IS
      SELECT
      FROM_ID, TO_ID, Field1, Field2, Field3
      FROM
      Table1  ;
BEGIN
 OPEN cur;
 LOOP
       FETCH cur INTO
        l_FROM_ID,
        l_TO_ID,
        l_Field1,
        l_Field2,
        l_Field3 ;

      UPDATE 
        table2 t
      SET
        t.field1 = l_field1, 
        t.field2 = l_field2,
        t.field3 = l_field3      
      WHERE  t.id_num >= l_FROM_ID and t.id_num <= l_TO_ID;
       commit;
    END LOOP;
    CLOSE cur;
end;

Any ideas on how to do it more efficient?

2
sqloraclecursorquery-performancesql-tuning

2 Answers

0
votes

There are many ways to accelerate this. First of all, I'd try just

update Table2 t2 set
  (field1, field2, field3) = (select field1, field2, field3 
                              from Table1 t1 
                              where t2.id_num between t1.from_id and t1.to_id)
0
votes

if your database server configured as OLAP(like etl /elt process). Instead of Updating you can create new table with the values you want and drop the existing table.

this way would be better for large tables.

Create table Newtable nologging pctfree 0 as select t1.field1, t1.field2, t.field3,..t2.columns you want to retain from table1 t1, tab2 t2 where t1.form_id=t2.to_id.

I hope this helps

Thanks Thangamani Eraniyan