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;

 

INSERT /*+ APPEND */ INTO …

 

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

 

Conclusions;

  •     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.

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s