The High Water Mark (HWM) is the last block in the table that Oracle has written a row to. When Oracle reads the...
entire table, called a full table scan (FTS), the FTS reads from the first block to the HWM. Oracle will do this even if you subsequently delete all of the table's rows. An FTS reads from the first block to the HWM. If you add enough data to fill up all blocks below the HWM, Oracle will bump up the HWM. But Oracle does not reset the HWM when you delete rows. In Oracle 10g, there is a new option to get Oracle to lower the HWM, but this command can be time-consuming as rows of data will have to move around.
The PCTFREE and PCTUSED parameters have nothing to do with the HWM. The PCTFREE parameter instructs Oracle to leave that percentage of the block free. This way, when you later perform an UPDATE to a row in that block, and that update causes the row to get longer, the UPDATE will find some free space in the block to accommodate that longer row. If there is no free space in the block for that longer row, Oracle will move that row (called row migration) to a new block. Oracle will add a pointer to the row's new location. When someone reads that row, they read the pointer and then the new block which means they have performed two block accesses for that row. Row migration can cause performance problems for that very reason.
Once a table only has PCTFREE free space, then Oracle does not allow a new row of data to be added to that block. If you delete rows of data from that block, then Oracle will allow a new row to be added to that block once the amount of used space in that block falls below PCTUSED.
PCTFREE and PCTUSED control if Oracle will let a new row of data be inserted into that block or not. This has nothing to do with the HWM.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.