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.

About these ads

About sqlandplsql

This blog is to help the community to learn more about the Oracle, PL/SQL, Ubuntu, MySQL etc and few general topics. An attempt to explain all topics in a simple and elegant manner. Suggestions, comments, feedbacks and referrals are highly appreciated.
This entry was posted in Oracle, PL/SQL and tagged , , , . Bookmark the permalink.

2 Responses to High Water Mark Oracle

  1. Josh Andrews says:

    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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s