Understanding DELETE vs TRUNCATE In Oracle Database


In Oracle, Segment Space Management is managing the space within a segment. In a segment generally you have USED block & UNUSED blocks.

USED blocks are blocks where a insert operation took place.

UNUSED blocks are blocks which have never used in the segment for any DML operations.

If that be the case how does Oracle identify from which point there are unused blocks ?

There is where something called HIGH WATER MARK (HWM) which comes into picture.

  • HWM is the point at which data has been inserted into the table.
  • All blocks beyond the HWM in a segment are unused.
  • When a full table scan occurs, the optimizer read the block till HWM for that segment. —–> PERFORMANCE TIPS.
  • HWM demarcates between used blocks & unused blocks in a segment.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *