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?
Printable View
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?
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.
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)?
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.Quote:
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?
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?
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.Quote:
Also, don't updates create a problem with dead index leaf blocks (requiring periodic reindexing)?
Would like to see, how are u updating, through Cursor i.e row by row update or Using Update..select ?Quote:
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.
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.
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.Quote:
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
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?Quote:
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.
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.
Yes, that's exactly what it does.Quote:
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?
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.Quote:
Will Undo Store Blocks Or Rows Or Data Spcific to Columns?
You said that. ;)Quote:
If this is the case, then I would assume I dunno a bit of Oracle.
It it were to keep the exact data what has changed, then why on earth one would get Snapshot Error for long running query.Quote:
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
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?
I should admit, I am feeling satisfied :DQuote:
Originally posted by jmodic
You said that. ;)
Abhay.
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.Quote:
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.
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.Quote:
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?
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.Quote:
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
I think i need to take a long break now :rolleyes: :DQuote:
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.
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.