Home » SQL & PL/SQL » SQL & PL/SQL » updating 500,000 records 3,000 at a time
updating 500,000 records 3,000 at a time [message #250876] Wed, 11 July 2007 12:51 Go to next message
marks20101
Messages: 74
Registered: May 2005
Member
The following is a simple script but, I want to update 3,000 at a time. How can I achieve this? There is currently around 400,000 records, and to do this update at once may not be a good idea for the rollback segments, or at least I think that is correct.

ln_find_member             NUMBER(1);
   ln_record_count            NUMBER(7);
     

   CURSOR get_member_cur
   IS
      SELECT  prov_extrnl_id, prov_dim_pid
      FROM    prov_extrnl_id_lu
      WHERE   prov_extrnl_id_src_desc = 'Alternate ID 3';

BEGIN

   ln_find_member  := 0;
   ln_record_count := 0;


-- SELECT COUNT(*)
--      INTO   ln_find_member
--      FROM   prov_extrnl_id_lu
--      WHERE  prov_extrnl_id_src_desc = 'Alternate ID 3';
 
          
   FOR get_member_cur_rec IN get_member_cur LOOP
   
      ln_record_count := ln_record_count + 1;
--      if ln_record_count < 3 then

      BEGIN 

--            IF ln_find_member > 0     then
            if ln_record_count < 3  then
                 
            INSERT INTO prov_gdln_val_bridge (
               prov_gdln_val_bridge_pid,        
               prov_dim_pid,                    
               gdln_def_dim_pid,               
               gdln_def_ctgy_code,             
               gdln_def_code,                  
               prov_gdln_sid,                   
               prov_gdln_val_sid,               
               prov_gdln_val_code,              
               prov_gdln_val_txt,               
               prov_gdln_val_ts,                
               prov_gdln_val_nbr )
            VALUES  (    
               prov_gdln_val_bridge_seq.NEXTVAL,        
               get_member_cur_rec.prov_dim_pid,
--               (SELECT gdln_def_dim_pid 
--               FROM   gdln_def-dim
--               WHERE  gdln_def_code = 'NATPROVID'),
               '17162',
               '*',           
               'NATPROVID',                  
               '999',   
               '1'  ,               
               NULL ,              
               NULL ,               
               NULL ,                
               NULL );
     
      END IF; 
          
            IF ln_record_count > 3 THEN
               COMMIT;
--            ln_record_count := 0;

            END IF;
      END;  

      
          
--            COMMIT;

  
   END LOOP;

--   COMMIT;

--   EXCEPTION
--      WHEN OTHERS THEN
--         RAISE;

END prov_NPI_proc;


Thanks!
Re: updating 500,000 records 3,000 at a time [message #250878 is a reply to message #250876] Wed, 11 July 2007 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to do this update at once may not be a good idea for the rollback segments, or at least I think that is correct.

No this is not.
I update million rows at once on my laptop.
Moreover, it is a bad idea to commit inside a loop it is:
1/ non ANSI
2/ a good way to ORA-1555 error
3/ a waste of resource
4/ a good way to slow other users

Regards
Michel
Re: updating 500,000 records 3,000 at a time [message #250879 is a reply to message #250878] Wed, 11 July 2007 13:17 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
Okay, so it is alright to update that many records at once, just no commit inside a loop?

Thanks much!

[Updated on: Wed, 11 July 2007 13:20]

Report message to a moderator

Re: updating 500,000 records 3,000 at a time [message #250882 is a reply to message #250879] Wed, 11 July 2007 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think "get_member_cur_rec.prov_dim_pid" is deterministic (maybe it is a package variable?), so first put the result in a local variable there is no need to call it for each row.
Then "INSERT SELECT" do it.

But there is so many code in comment that finally it is not the real code.

Regards
Michel
Re: updating 500,000 records 3,000 at a time [message #250892 is a reply to message #250882] Wed, 11 July 2007 14:10 Go to previous messageGo to next message
marks20101
Messages: 74
Registered: May 2005
Member
I understand what your saying, but not sure on how to go about it? Never used Insert Into.

Can you provide a snipplet?

Thanks again!!
Re: updating 500,000 records 3,000 at a time [message #250896 is a reply to message #250892] Wed, 11 July 2007 14:19 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698

Regards
Michel
Previous Topic: why use varchar2(4000)
Next Topic: sum the next both tuples
Goto Forum:
  


Current Time: Thu Dec 08 04:13:14 CST 2016

Total time taken to generate the page: 0.17583 seconds