Wednesday, 7 January 2009

High Water Mark (Oracle)

High-water mark

High-water mark indicates the point up to which table or index has ever contained data.Let me explain .Suppose we create an empty table,the high-water mark would be at the start of the table segment

Now if we insert some data into the table segment, the HWM moves and it will be at position up to which the data is in the segment

If insert more data in the table segment, HWM moves further to point the position up to which the data is in the segment

Now if we delete the data from the table see the new position of HWM

As we can see above on deleting the data, HWM does not move. The drawback of this is that oracle always read the blocks up to high water mark in case of full table scan. You must have noticed that doing a count (*) on empty table, takes time to show you no rows. The reason for taking so much time is setting of HWM at higher position.

Now you must be thinking , how to set the high-water mark at lower position ?
The only method to set the HWM is to truncate a table.

Let us see how truncate set the HWM.

HWM is reset now , after truncating data. Table where is having lots of delete or insert operation going on, probably has High HWM. If the HWM is high, it is always advised to rebuild table segment for performance improvement.


  1. Krishna - Nice Explanation. If possible update it. with oracle 10g. we have segment shrink. instead of truncate.

  2. I always love~~ an explanation with picture.

    Can i ask a question?
    If we insert data after when HWM already exist, where will the data be inserted?

    After the data (red block)?
    Or after the HWM (green block)?

  3. Nice explanation, but HWM can also be reset by reorganizing the table ( ALTER TABLE MOVE ), or using the SHRINK commands of ORACLE 10g.