Insert Append Can Be A Wasted Space

Today has triggered an alarm about lack of space in a tablespace, examining what there are into the tablespace, a table appears it’s a historical data, occasionally is a process  deleting from this table, but to grow ahead this is not reusing space. After reviewing the ASH I see basically two types of DML statements are made against this table, a delete that responsible for maintaining only the last year and a insert keeping the most recent information. Seeing the same judgment was confirmed my suspicions that uses an append, the statement is as follows;




This always inserting HWM above. All the space behind the High Water Mark is being lost.  Of course, this is the worst case.



  •     To recover the space would have to rebuild the table.
  •     You can reclaim space by removing the append hint, but it perform the insert more slower, especially if it is massive like this case.





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s