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

Thread: Better to delete and insert than to update?

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I can imagine (please underline the word, imagine) why DELETE+INSERT may work faster than UPDATE on a table with 1000 columns.
    First of all Oracle cannot read all the 1000 columns row in a single I/O. Oracle reads ( i would say reconstruct the row )first 255 columns (the barrier set in old release), then next 255 columns, then next 255 columns and finalyy the last 235 columns in the 4th read even if all data resides in a single oracle block.

    I think UPDATE may involve again reconstructing the row in 4 pieces, that may hurt performance.

    I have not tested my thoery. If time permits, I will do it.

    Tamil

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    I can imagine (please underline the word, imagine) why DELETE+INSERT may work faster than UPDATE on a table with 1000 columns.
    First of all Oracle cannot read all the 1000 columns row in a single I/O. Oracle reads ( i would say reconstruct the row )first 255 columns (the barrier set in old release), then next 255 columns, then next 255 columns and finalyy the last 235 columns in the 4th read even if all data resides in a single oracle block.

    I think UPDATE may involve again reconstructing the row in 4 pieces, that may hurt performance.

    I have not tested my thoery. If time permits, I will do it.

    Tamil
    Yes, this sounds reasonable enough.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    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).


    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.
    I think i need to take a long break now

    Coz till now i thought its the block thas put into rollback(my bad i misread/misunderstood from the docs), i really have to have some reading on the UNDO (It always F* me)

    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"

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