High Water Mark Oracle

In simple term High water mark is a logical mark in the memory area to separate blocks having data and no data. As you know that the blocks represent the most granular form of data in memory.

When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.

See below given logical and pictorial representation of high water mark when a table data insert/update/delete/truncate

High Water Mark Oracle
High Water Mark Oracle

By default High Water Mark starts at the first block for new tables. When table gets data via insert/update more blocks being used High Water Mark moves forward. When data get deleted, High Water Mark remain same even though blocks doesn’t have any data.

Delete has no impact on high water mark and Truncate reset the High Water Mark. This is the one difference between delete and truncate ( Read more difference between Delete and Truncate )

When full table scan takes place, Oracle scans all blocks below High Water Mark even though it has no data.

5 responses to “High Water Mark Oracle”

  1. Between steps 4 and 5 I am not so sure that the high water mark gets increased. Doesn’t it get left the same as there is enough empty space below the high water mark to insert the remaining data?

    1. I have mentioned it in general way. Thanks for the comment.

  2. I have mentioned it in general way. Thanks for the comment.

  3. hi,
    please provide difference in using high water mark and index as with index we can scan table quicker..Also provide syntax in using this

  4. please provide the command how to scan columns using hwm