1 2 Previous Next 15 Replies Latest reply: Feb 5, 2014 9:52 AM by jgarry RSS

How to update a table with 100 million rows

user12240205 Explorer
Currently Being Moderated

There is a table with 100 million rows.

 

A lot of users are doing inserts/updates/deletes to this table every second.

 

We want to update only 1 column of that table. But all rows in that table should be updated.

 

My question is: Can you do this without asking all users to log off.

Because we can

(1.) Use a single update statement to update the column. But that will generate lot of rollback information and we will run out of space. We might get erros like this ORA-01650:UNABLE TO EXTEND ROLLBACK SEGMENT.  Also, even if you have disk space, what if some users have locked rows of this table. Thus update will hang.

Also, what about inserts which happen while our update statment is running? We will not update these records.

 

(2) If we use a CTAS to create another table, that will solve the user locking issue, but for once that will take a lot of space for the new table. Even if you have space, while the CTAS is going on, other users ill be inserting, updating and deleteting rows of that table. Those changes will not be in the new table created by the CTAS. So again we have another problem.

 

(3.) What if you update the original table in batches. Assuming the primary key is a number, we can update in 100k batches and commit. This will generate very little rollback, but still have the problem of other users locking rows. If another user has locked a row in a batch, that update will hang.  Also,others users will still be inserting new rows. Then these rows wiil not  be updated since we miss them.

 

(4.) Using someghing like DBMS_PARALLEL_EXECUTE procedure, we can divide the table into chunks anb update. But sill face a problem if some others users have locked rows. Also, new rows inserted while the update is going on will not be updated.

 

How can we solve this problem?  Can this be solved only by asking all the users to logoff and doing the update? How can you make sure that a table CANNOT be inserted/updated or deleted to by anybody. Like is there a command or do we have to write a trigger (like a before insert / update / delete row trigger) to disable ang DML?

 

DBA gurus, over to you.

  • 1. Re: How to update a table with 100 million rows
    saratpvv Journeyer
    Currently Being Moderated

    you missed partations - Why can't partations will be reliable over here

  • 2. Re: How to update a table with 100 million rows
    sb92075 Guru
    Currently Being Moderated

    LOCK TABLE employees   IN EXCLUSIVE MODE    NOWAIT;

  • 3. Re: How to update a table with 100 million rows
    user12240205 Explorer
    Currently Being Moderated

    sb92075 wrote:

     

    LOCK TABLE employees   IN EXCLUSIVE MODE    NOWAIT;

    Yes, but what if 100s of users have locked 100s of records. Will this work?

  • 4. Re: How to update a table with 100 million rows
    JohnWatson Guru
    Currently Being Moderated

    I would use

    ALTER SYSTEM QUIESCE RESTRICTED;

    wait until you get the quiesce, lock the table, and then unquiesce and do your update.

  • 5. Re: How to update a table with 100 million rows
    Martin Preiss Expert
    Currently Being Moderated

    maybe you can use dbms_redefinition for this task - as shown by Tom Kyte: http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48asktom-089856.html.

  • 6. Re: How to update a table with 100 million rows
    sb92075 Guru
    Currently Being Moderated

    user12240205 wrote:

     

    sb92075 wrote:

     

    LOCK TABLE employees   IN EXCLUSIVE MODE    NOWAIT;

    Yes, but what if 100s of users have locked 100s of records. Will this work?

    maybe, yes

    most likely, no

  • 7. Re: How to update a table with 100 million rows
    user12240205 Explorer
    Currently Being Moderated

    JohnWatson wrote:

     

    I would use

    ALTER SYSTEM QUIESCE RESTRICTED;

    wait until you get the quiesce, lock the table, and then unquiesce and do your update.

    So, are you saying that you cannot update this table if others have locks on records?

    Also, others are inserting new records. So you can no way do the update until you get exclusive lock on the whole table

    or asking all users to logoff?

  • 8. Re: How to update a table with 100 million rows
    JohnWatson Guru
    Currently Being Moderated

    No, am not saying any of that. I am saying how I would solve the problem. You do not have to use my way. Though it is probably the best suggested so fa. You do not appear to have suggested or tested anything yourself yet.

  • 9. Re: How to update a table with 100 million rows
    Martin Preiss Expert
    Currently Being Moderated

    some additional comments on Jonathan Lewis' Scratchpad: http://jonathanlewis.wordpress.com/2008/07/28/big-update/. dbms_redefinition is mentioned in the comments - and as far as I can see it's the only option to do the update online without generating lots of undo/redo (as a simple update would do).

  • 10. Re: How to update a table with 100 million rows
    rp0428 Guru
    Currently Being Moderated

    Also, others are inserting new records

    Stop! Now you are changing things a bit.

     

    First you said this:

    We want to update only 1 column of that table. But all rows in that table should be updated.

    So obviously any rows inserted after you do your update will NOT have your update.

     

    Why do you need to update existing rows but do need to have new rows updated the same way?

     

    Are you really just trying to modify the column to NOT NULL and use a DEFAULT value?

     

    What PROBLEM are you trying to solve? Please clarify the actual requirements.

  • 11. Re: How to update a table with 100 million rows
    Aman.... Oracle ACE
    Currently Being Moderated

    user12240205 wrote:

     

    JohnWatson wrote:

     

    I would use

    ALTER SYSTEM QUIESCE RESTRICTED;

    wait until you get the quiesce, lock the table, and then unquiesce and do your update.

    So, are you saying that you cannot update this table if others have locks on records?

    Also, others are inserting new records. So you can no way do the update until you get exclusive lock on the whole table

    or asking all users to logoff?

    TBH I haven't understood your requirement at all. Why on earth, you want to update a table that's been actively in use to be updated with 100 million rows for just one column? And for whatever reasons, you want to do it, what about the data that's being inserted when the update would be going on ? Doesn't look all that correct to me.

     

    Aman....

  • 12. Re: How to update a table with 100 million rows
    user12240205 Explorer
    Currently Being Moderated

    Aman.... wrote:

     

    user12240205 wrote:

     

    JohnWatson wrote:

     

    I would use

    ALTER SYSTEM QUIESCE RESTRICTED;

    wait until you get the quiesce, lock the table, and then unquiesce and do your update.

    So, are you saying that you cannot update this table if others have locks on records?

    Also, others are inserting new records. So you can no way do the update until you get exclusive lock on the whole table

    or asking all users to logoff?

    TBH I haven't understood your requirement at all. Why on earth, you want to update a table that's been actively in use to be updated with 100 million rows for just one column? And for whatever reasons, you want to do it, what about the data that's being inserted when the update would be going on ? Doesn't look all that correct to me.

     

    Aman....

    You have a good point. I am a Forms & PL/SQL developer. This question was asked from me during an interview last month.

     

    As  you said, for example, if you want to update a column to NOT NULL then, you don' have to worry about inserts since we can use a DB trigger for that. But I think we might be able to ignore the inserting records part and concentrate only on the update. The Jonathan Lewis page gave some good suggestions.

  • 13. Re: How to update a table with 100 million rows
    rp0428 Guru
    Currently Being Moderated
    This question was asked from me during an interview last month.

    Unfortunately the way that question was presented makes it a very poor question for an interview setting. IMHO.

    A lot of users are doing inserts/updates/deletes to this table every second.

     

    We want to update only 1 column of that table. But all rows in that table should be updated.

     

    My question is: Can you do this without asking all users to log off.

    The simple answer is 'yes' - but I doubt if that is the answer the interviewer is looking for: it doesn't convey any real useful info about what you know or don't know.

     

    That is a very poor interview question for several reasons.

     

    First it is a very unlikely use case as presented. It makes no sense to update the existing data without having a way to have NEW rows updated automatically in some way.

     

    The closest use case I can think of would be if that column was going to be modified to have a DEFAULT value and you needed to update the existing rows to use that new default value. But for that you would not update ALL rows but only those that are currently NULL. And you would also make the column NOT NULL.

     

    So I wouldn't ask that question in an interview - I could care less if you knew if that could be done or not.

     

    My question would be more along the lines of: what impact will it have on the users/system if you TRIED to do that online?

     

    In an interview you should ALWAYS ask for more information if there is not enough to properly answer the question.

  • 14. Re: How to update a table with 100 million rows
    Aman.... Oracle ACE
    Currently Being Moderated

    It would be, as Rp mentioned, a poor question to be asked in an interview. If I would be asked this, I would had asked a counter, why it is a requirement anyways? And I would had loved to hear the solution and probably, done a little counter-interview .

     

    Aman....

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points