Category Storage

Reclaim space used by table and index after data deletion

by Snehashish Ghosh

Scenario


 Significant amount of data (more than 80%) has been deleted from a database schema. However size of schema has not come down proportionately with the volume of data deleted.

Solution


 1. Perform Online Segment Shrink

The Online Segment Shrink process consolidates fragmented free space below the high water mark and compacts the segment. After compaction, the high water mark is moved, resulting in new free space above the high water mark. That space above the high water mark is then deallocated.

1.1 Benefits

  • Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
  • The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) p...
Read More