Hi,
In Oracle databases, what is this high water mark? What impact does it have on the full table scan?

Well, HWM is an indication that segregates used and unused blocks. Above the high water mark you find the unused blocks and below it the used blocks.

Used blocks can be
1. Free - unoccupied
2. Used - occupied

As the data grows in a table, the high water mark starts moving upward. The only way to bring it down is to rebuild or truncate the table.

And a full table scan reads from the beginning of the table to the high water mark.

When you insert data into a table and then delete it later, the high water mark doesnt move down. So when you insert a million records into a table, the HWM moves up. A full table scan will scan as if there are a million rows. When you delete 0.9 million records from it, the high water mark doesnt come down. A full table scan will still scan as if there are a million records.

The ideal way to handle this is by repacking the table

alter table {TABLE_NAME}
move;

And keep 2 more things in mind.
1. Indexes size will remain same. That has to be repacked.
2. Indexes will be invalidated.

So, rebuild the indexes to fix these 2 issues.

Hi,
I use Microsoft Excel to track my finances and investments. I love it. Pls see the picture below for a sample chart I use. (this is one of the main reports that I use).

Click on it for a larger picture.