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.