DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Urgent rownum, update million records

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I have to do number of updates on a table which has million records.
    eg.
    Update mwebMatrix
    Set Mat_Approved = 10
    Where Mat_Category in(1,3) ;
    commit;

    If I do this, it takes hours and nothing happens.
    So I broke these statements in small parts..
    Update mwebMatrix
    Set Mat_Approved = 10
    Where Mat_Category = 3 and mat_id between 1 and 250000;
    commit;
    Update mwebMatrix
    Set Mat_Approved = 10
    Where Mat_Category = 3 and mat_id between 250001 and 350000;
    commit;
    Update mwebMatrix
    Set Mat_Approved = 10
    Where Mat_Category = 3 and mat_id between 350001 and 450000;
    commit;


    Even these once are taking a long time.. and I don't know if anything is getting updated or not..
    I can't do select count(*).. either.

    Is there any faster way to do this.
    How about a cursor with loop and commits for a batch or do you think that will be bad ?

    Can I use ROWNUM function to do the batch updates, like in thread:
    http://www.dbasupport.com/forums/sho...0&pagenumber=2

    thanks for help
    Sonali
    Sonali

  2. #2
    Join Date
    Jan 2001
    Posts
    318
    Oh, I forgot to mention..
    mat_category is indexed and mat_id is PK.

    One more question:
    I am doing one of the updates..
    update mwebMatrix
    set Mat_Role_ID = 1
    where Mat_role_id is null;
    commit;

    I want to see what is happening with this update..because its been 2 hours since I ran it.. and its still not finished.
    I did select * from v$session and the session is active.
    I cannot do.. to see if any records are getting updated because it hungs and never comes back.
    select count(*) from mwebmatrix where mat_role_id = 1; show

    When I did this it said..
    SQLWKS> Select event from v$session_wait
    2> where sid=11
    3>
    EVENT
    -----------
    db file sequential read
    what does this mean ?

    thanks

    [Edited by sonaliak on 06-28-2001 at 10:27 AM]
    Sonali

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    for this count of records is loop far better method.
    But you should use ROWID, not ROWNUM:

    ...
    FOR rec IN (SELECT ROWID FROM mwebMatrix WHERE Mat_Category in(1,3) )
    LOOP
    UPDATE mwebMatrix SET Mat_Approved = 10 WHERE ROWID = rec.ROWID; --you can also select PK instead of ROWID and change WHERE clause to "mat_id=rec.mat_id"
    COMMIT --or you can count rows and commit every 100, 1000 ...
    END LOOP;
    ...

    I can't promise you it'll be faster, but you can see progress from another session since small batch is commited, not the whole update. Another advantage: if you break this script you can start it again without waiting for rollback.
    The piece of code above is without warranty, I haven't tested it ...

    And your second question...
    If a session makes long update, other sessions aren't able to see changes until update is commited.
    Try to estimate count of records with "Mat_role_id is null" and consider using of loop with batch commit again.

    The table seems to be large and Mat_role_id is probably not indexed, so full table scan is the only method to find records with "Mat_role_id is null" and that's exactly what you see in v$session_wait.

    HTH,
    Ales


  4. #4
    Join Date
    Jan 2001
    Posts
    318
    Some how I managed to get the updates done.. though it took really long long time.. I could not make it any faster.
    Mat_role_id was indexed.

    But now I want to do a delete:
    delete from mwebmatrix where mat_category = 2000;
    To make it faster I am doing it in batches like..
    delete from mwebmatrix where mat_category = 2000 and mat_id <500000;

    mat_id is PK and mat_category is FK and indexed.

    I am the only one using this database at this time and this is the only query I will be running. Do you think dropping the index on mat_category will make it faster ?

    thanks

    Sonali

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