Updating a big table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Updating a big table

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    I am testing to do a update on emp table .
    I added dname column to emp table.
    Made emp table to about 500000 rows and trying to do an update so that there is commit for 100 rows ...

    Update is happening ...I can select dname .But it is going in a loop .
    I have set serveroutput on
    but then also 'Commit in progress' is not getting displayed at all

    why ???

    Declare
    cnt number := 0 ;
    Cursor loc is
    select b.dname dname ,B.deptno deptno
    from emp a, dept b
    where a.deptno=b.deptno
    for update of a.dname ;
    Begin
    For c1 in loc
    loop
    update emp set dname = C1.dname
    where deptno = C1.deptno ;
    cnt := cnt + 1 ;
    If cnt = 100 then
    cnt := 0 ;
    commit ;
    dbms_output.put_line ('Commit in progress') ;
    end if ;
    end loop ;
    end ;
    /


    [Edited by Subha on 02-26-2002 at 02:31 PM]

  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    You need to use the WHERE CURRENT OF or ROWID to make sure that you are updating the current cursor record with the dbname you are getting from the cursor.

    [Edited by kris109 on 02-26-2002 at 04:51 PM]
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    All outputs made by dbms_output are stored on server side and sent to client just after finishing the job. Thus you cannot see progress of that script, just final report.
    For watching progress you could use a log table and insert a row in it instead of putting a line by dbms_output. Then you can watch this log table from another session.

    BTW, updating big table in this fashion is very slow. The task you are doing is better and faster done with a single or several UPDATE statements.
    I also suppose that iside the loop you are updating all emp rows with that deptno, not just current row. Then you don't do 500000 updates but 500000*rows_of_deptno.
    Use WHERE CURRENT OF or ROWID as kris109 proposed.

    I'm not sure, but with this approach you can also get a "snapshot too old error".

    HTH,
    Ales

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    For the dname pulled by the cursor, the rows in the emp table are being updated atleast half a million times. I am sure this is not what the poster wants.

    [Edited by kris109 on 02-27-2002 at 04:12 PM]
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  5. #5
    Join Date
    Dec 2000
    Posts
    23
    first the problem is that rollback segment is not sufficient.
    first u assign the temporary rollback segment to the current transaction as
    SET TRANSACTION USE ROLLBACK SEGMENT

    then run that procedure.
    it will work according to ur requirement.

    the problem is that cursor select statement returns 500000*
    means if dept table contains 5 rows then that stament returns 2500000 rows.
    inside the loop for each row, it searches 500000 rows in emp table whether deptno is matching or not.
    so for 100 rows it searches 500000 * 100 rows which is a very very huge transaction.
    so the problem is with rollback segments.
    that rollback segment should be very large in size

    Vth Regards,
    Bhaskar akkala....
    sridhar garige

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width