Better to delete and insert than to update?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Better to delete and insert than to update?

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Better to delete and insert than to update?

    On a very large table with 1000 columns per row, is it better to delete the original row and insert a new (updated) row than to update the original row?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why would it be better to delete+insert compared to update?

    DELETE+INSERT certanly means more I/O operation compared to UPDATE, no doubt about that. Plus when you update the original row the PK index remains intact (you are not updating PK columns, are you?), with delet+insert you are modifying PK index twice!

    The only situation when I could immagine delet+insert would be prefered over update would be when the update would inevitably cause row migration. And even then it would be more of the design/block space management isue, so I would try to correct those isues rather than going for delet+insert.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Keep an open mind with me for a moment...

    Fact: Updates on a 1,000 column table are taking FOREVER.
    Fact: Delete's and insert's to the same table are really quick.

    I haven't benchmarked the difference, but it seems that "kill and fill" might be faster overall. Perhaps it's the 1,000 columns? What do you think?

    Also, don't updates create a problem with dead index leaf blocks (requiring periodic reindexing)?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by KenEwald
    Fact: Updates on a 1,000 column table are taking FOREVER.
    Fact: Delete's and insert's to the same table are really quick.

    I haven't benchmarked the difference, but it seems that "kill and fill" might be faster overall. Perhaps it's the 1,000 columns? What do you think?
    Hm, have to admit I've never been involved with a 1,000 columns table (not even close!), however I simply can't imagine the reasons why updates could be slower than insert+update. If you realy empiricaly noticed "kill and fill" is fater and if you ever find out the reasons (or at least logical explanation) I'd be very interested to know them.

    Also, you were not very specific about the nature of your updates. Are you (on everage) updating one column, few columns, lot of columns, majority of coulumns per update?
    Also, don't updates create a problem with dead index leaf blocks (requiring periodic reindexing)?
    Update means "delete" the old key from one index block and "insert" the new index key in another (or the same) index leaf block. Delete + insert means "delete" the old key from one index block and "insert" the new index key in another (or the same) index leaf block. So absolutely no difference. besides, there is no such thing as dead index leaf blocks that would require periodic reindexing. When all the keys are deleted from index leaf block that leaf block can and will be reused.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by KenEwald
    Keep an open mind with me for a moment...

    Fact: Updates on a 1,000 column table are taking FOREVER.
    Fact: Delete's and insert's to the same table are really quick.

    Would like to see, how are u updating, through Cursor i.e row by row update or Using Update..select ?

    Well this is to Jurij, Wont Update mean as good as DELETE+INSERT?

    coz, old image has to be written in Rollback which almost equals DELETE, then new image of data has to be written/modified to the blocks in question which almost equals INSERT...Ofcourse with Updates
    I/O < I/O for Insert+Delete & so goes for the GETS.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    coz, [with update] old image has to be written in Rollback which almost equals DELETE, then new image of data has to be written/modified to the blocks in question which almost equals INSERT
    Well, only if you are updating all of the columns in a row. But if you update only a small fraction of the entire row's data (for example only one column), then only the changed bytes of that row are stored in the undo. Also, only a small change needs to be recorded in the redo. But with DELETE, the whole record needs to be stored in the undo and almost no information in redo, and then with INSERT almost no information is stored in undo but the whole row must be stored in redo. So if you change only a few columns in a row, the UPDATE wins over DELETE+INSERT both on the amount of generated undo and redo.

    That's the reason why I asked how many columns of the row he is usualy updating.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Well, only if you are updating all of the columns in a row. But if you update only a small fraction of the entire row's data (for example only one column), then only the changed bytes of that row are stored in the undo. Also, only a small change needs to be recorded in the redo. But with DELETE, the whole record needs to be stored in the undo and almost no information in redo, and then with INSERT almost no information is stored in undo but the whole row must be stored in redo. So if you change only a few columns in a row, the UPDATE wins over DELETE+INSERT both on the amount of generated undo and redo.

    That's the reason why I asked how many columns of the row he is usualy updating.
    Will Undo Store Blocks Or Rows Or Data Spcific to Columns?

    I wonder, how it will just keep the data pertaining to the Column/s In Question for Updates?

    Does Oracle Break the block and peep into it, find the row, and then find the column/columns and keep only that data in UNDO?

    If this is the case, then I would assume I dunno a bit of Oracle.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Will Undo Store Blocks Or Rows Or Data Spcific to Columns?

    I wonder, how it will just keep the data pertaining to the Column/s In Question for Updates?

    Does Oracle Break the block and peep into it, find the row, and then find the column/columns and keep only that data in UNDO?
    Yes, that's exactly what it does.

    Will Undo Store Blocks Or Rows Or Data Spcific to Columns?
    Rows or data specific to couluns - depending what operation has been performed (DELETE or UPDATE). For INSERT it stores only ROWID of the inserted row.

    If this is the case, then I would assume I dunno a bit of Oracle.
    You said that.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Yes, that's exactly what it does.

    Rows or data specific to couluns - depending what operation has been performed (DELETE or UPDATE). For INSERT it stores only ROWID of the inserted row
    It it were to keep the exact data what has changed, then why on earth one would get Snapshot Error for long running query.

    Or Lemme put things like this..

    Then, why would there be a term called CONSISTENT get or Rather how/why do we define Consistent Get?

    Originally posted by jmodic
    You said that.
    I should admit, I am feeling satisfied

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    It it were to keep the exact data what has changed, then why on earth one would get Snapshot Error for long running query.
    Gee, are we starting with basics about ORA-1555 again! Oh please, no! Why don't you read again about snapshot to old error to refresh your memory. There is plenty of stuff about that everywhere.

    In short, ORA-1555 happens when Oracle is unable to reconstruct the query's SCN consistent immage of changed block. Again, it allways reconstruct the block with the help of UNDO stored information, it doesn't simply read the whole block from UNDO (as it doesn't store whole blocks there).

    Originally posted by abhaysk
    Or Lemme put things like this..

    Then, why would there be a term called CONSISTENT get or Rather how/why do we define Consistent Get?
    What so strange about that? CONSISTENT GET means oracle compares SCN of a block in buffer cache with the SCN of a query that requested a consistent get. If SCN in a buffer is older than the SCN of the query then it uses that block. If SCN of a buffer is newer then SCN of the query, then Oracle finds the neccessery information in rollback segments and reconstructs the buffer to exactly the same state as it was when the query started. Thus you get CONSISTEN immage of the block. If it can not find the needed information from rollback you get snapshot too old error.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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