Oracle Database Administration: The Essential Refe

The highwater mark

For each object, Oracle also maintains a record of the highest relative block of the table used to hold data. This highwater mark is maintained in multiples of five blocks and is not reset unless the TRUNCATE command is executed.

When Oracle performs operations requiring a full table scan, such as SELECT COUNT(*), all blocks up to and including the highwater mark are read. If a table is created with 50,000 rows occupying 10,000 blocks, and those rows are subsequently deleted, the highwater mark will remain at 10,000, and a SELECT COUNT(*) command will read all 10,000 blocks even though they are all empty.

An even worse scenario is possible. Suppose that a table contains 50,000 rows, and the first 49,000 rows are then deleted. The blocks corresponding to the deleted data are placed at the end of the free block list. When the next INSERT statement is executed, Oracle finds the first block on the free block list, which is beyond the highwater mark. The effect is that all the free space (49,000 rows worth) is ignored, and the physical table becomes bigger. Full table scans and other similar operations still have to read all the empty blocks, and performance is significantly impacted. If you use SQL*Loader with the direct path option, these loads always begin at the highwater mark, so the table size may grow while leaving significant amounts of free space unused.

To easily determine the current value of the highwater mark, use the following formula after analyzing the ...