14.1 What is the High Water Mark?

---

Often a table, index, or database object is referred to as a segment. The high water mark of a segment is the maximum number of blocks ever used in that segment. Space can be removed from segments by deletions, fully or partially freeing up blocks. Therefore, the high water mark of a table is the highest block number ever used by that table for data, not allocated space. The high water mark is not reset when data is deleted. When a full table scan is performed each block is read up to the table's high water mark. Therefore, if a new table with 100 free blocks, has inserts filling 70 blocks, followed by deletes freeing 20 blocks, then that table's high water mark is 70 blocks and not 50 blocks (70 - 20). A full table scan on this table will physically read 70 blocks even though 20 of those 70 blocks have been deleted. This is why it is good for performance to avoid full table scans and use indexes, especially for large tables with extensive DML deletion activity.

It is true that full table scans will be faster than index plus table reads, particularly for index range scans, if enough rows are read. However, the point about the high water mark is that the more deletions occur, without reuse of fully or partially freed up blocks, the more likely a full table scan will read deleted blocks. Small transactional OLTP databases do not benefit generally from full scans of any kind unless tables are very small and static.

---