High-water mark (HWM)

High-water mark is an indicator or pointer up to which table or index has ever contain data. Suppose we create an empty table , the high-water mark would be at the beginning of the table segment

Unused block or extent occupied by the table segment on initial creation.

After inserting data in the table segment , the HWM moves and point the position up to which the data is in the segment

DATA -------------------

Un-Used Block

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

------ DATA-----------------

Un-Used Blocks

Now let us delete the data and see the pointer of HWM

DATA -------

Empty Blocks

Un-Used Blocks

Full Table Scan

As you seen above by deleting the data , HWM does not move. The main disadvantage of this is that oracle always read the blocks up to high water mark in case of full table scan . You may have ever notice that doing a count(*) on empty table , takes time to show you 0 rows. The reason for delay is setting of HWM at higher position.

Now the question arises in front of us , how we set the high-water mark at lower position ?

The one way to set the HWM is to truncate a table (but all row will be deleted !). Let us see how truncate set the HWM.

No data in the segmen

HWM is reset now , after truncating data.

Another way is SEGMENT SHRINKING (it will hold all data) . To see how it works please go through the article on Oracle tuning on this blog

Table where lots of deletion or insertion takes place , probably has High HWM. If the HWM is high , it is better to rebuild table segment for performance sake.

Calculate the HWM as follows

HWM % = (HWM BLOCKS - Actual DATA BLOCKS / HWM BLOCKS ) * 100