Home » SQL & PL/SQL » SQL & PL/SQL » SQL Mass update - dumb question (Oracle 9i)
SQL Mass update - dumb question [message #353060] Fri, 10 October 2008 16:08 Go to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Got a dumb question.. I need to do a mass update of a table which just got one field added to it. Here's the pseudo breakdown:

I have two tables:
T1 has 2 columns: "id" and "field1"
T2 has 1 column: "id". But now I added "field" column to it, basically making it match T1.

I need to update T2 with all the "field" values from T1, where T1.id = T2.id

Here's the kicker - there are about 90 million rows, so I don't think a simple UPDATE will work (rollback segments filling up and crashing the update).

Please let me know any advice on this!
Re: SQL Mass update - dumb question [message #353065 is a reply to message #353060] Fri, 10 October 2008 17:43 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Never mind, think I have a solution. Problem is that it runs very slowly, so I may need to play with the Commit-Max field to change how often I'm commiting. Thoughts on that?

Here is the code I am using:

set serveroutput on
 
declare
 
V_CNTR                        NUMBER         := 1;
V_COMMIT_CNTR                 NUMBER         := 1;
V_COMMIT_MAX                  NUMBER         := 1000;
 
cursorObj                     SYS_REFCURSOR;
LOCAL_FIELD                   NUMBER;
LOCAL_ID                      NUMBER;
 
begin
 
    dbms_output.enable(100000);
 
    dbms_output.put_line('BEGINNING prc');
 
  --SELECT ROWS FROM T1
     OPEN cursorObj FOR
      SELECT T1.FIELD, T1.ID
      FROM T1, T2
      WHERE T1.ID = T2.ID;
      
     LOOP 
      FETCH cursorObj
       INTO LOCAL_FIELD, LOCAL_ID;
       
     EXIT WHEN cursorObj%NOTFOUND;
 
     IF V_COMMIT_CNTR > V_COMMIT_MAX THEN
      COMMIT;
      V_COMMIT_CNTR := 1;
     ELSE
      V_COMMIT_CNTR := V_COMMIT_CNTR + 1;
     END IF;
 
           -- PERFORM UPDATE
           UPDATE T2 SET FIELD = LOCAL_FIELD
             WHERE ID = LOCAL_ID;
 
     V_CNTR := V_CNTR+1; --INCREMENT COUNTER
    END LOOP;
 
    CLOSE cursorObj;
 
    COMMIT; --LET COMMIT AND GET OUT
    V_COMMIT_CNTR := 1;
 
     dbms_output.put_line('END OF SCRIPT');  
 
end;

Re: SQL Mass update - dumb question [message #353069 is a reply to message #353065] Fri, 10 October 2008 21:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since Oracle stores all columns of a table in the same block (some exceptions with LOBs and IOTs), even though you are updating just one column, you are actually re-writing every row in its entirety on disk.

Here are some suggestions on bulk updates.

The absolute FASTEST way is to rebuild the table using CREATE TABLE new_table AS SELECT ... FROM whatever. This will not be subject to UNDO / Rollback restrictions - you can write as many rows as you have space to store.

Ross Leishman
Previous Topic: QUERRY Help
Next Topic: Connect By Prior and nocycles ...
Goto Forum:
  


Current Time: Wed Dec 07 22:11:32 CST 2016

Total time taken to generate the page: 0.05492 seconds