APPEND hint and Table space management

This case is written for an application team to show – how to club DELETE and table rebuild while using INSERT /*+ APPEND */ for daily loading. The original problem was, tablespace was growing fast everyday even though application was DELETing old records.

As we know, when ever we use APPEND in INSERT or APPEND in sqlldr, the records will be placed above the HWM (High Water Mark) after formatting the new blocks, leaving the free unused blocks below HWM as shown in the below test case.

So, a DELETE 100% data and insert same amount of data caused the table grow another 18 extents or 1872 KB. The reason is, records get INSERTed above the HWM water mark – means new blocks added to the table to INSERT the new records.

That shows clearly, a DELETE will never release the space from the table or it will never re-use for INSERT /*+ APPEND */ or sqlldr with APPEND clause. It is not just space usage, the SELECT or any table access will take more time as it need to scan the blocks below the HWM.

As you see above it took 710 consistent reads to report zero rows while just 3 consistent reads after a TRUNCATE.

The question is, in the case how can we release unused space from a table after removing the records. TRUNCATE may not be possible if we are DELETing only part of the table. So, I did use the PARTITION way. The application requirement was to DELETE records below a specific DATE. So, I have created the table using a RANGE PARTITION and INSERTed sample records using /*+ APPEND */ hint.