High Water Mark

High-water mark is an indicator to which table or index has ever contained data.

This is a term used with objects stored in the database. If you envision a table for example as a ʹflatʹ structure, as a series of blocks laid one after the other in a line from left to right, the high water mark would be the right most block that ever contained data. For example:

This shows that the high water mark starts at the first block of a newly created table. As data is placed into the table over time and more blocks get used, the high water mark rises. If we delete some (or even all) of the rows in the table, we might have many blocks that no longer contain data, but they are still under the high water mark and will remain under the high water mark until the object is rebuilt or truncated.

The high water mark is relevant since Oracle will scan all blocks under the high water mark, even when they contain no data, during a full scan. This will impact the performance of a full scan especially if most of the blocks under the high water mark are empty. To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows). Do a SELECT COUNT(*) from this table. Now, DELETE every row in it and you will find that the SELECT COUNT(*) takes just as long to count zero rows as it did to count 1,000,000. This is because Oracle is busy reading all of the blocks below the high water mark to see if they contain data. You should compare this to what happens if you used TRUNCATE on the table instead of deleting each individual row. TRUNCATE will reset the high water mark of a table back to ʹzeroʹ. If you plan on deleting every row in a table, TRUNCATE would be the method of my choice for this reason.