Table High water marks and Full Table Scans

When we create an Oracle table we must specify a STORAGE clause. This storage clause determines the initial and next extent sizes, and Oracle allocates data blocks within the tablespace based upon the values for these parameters. To prevent the database from reading into storage that has no rows, Oracle always keeps track of the table high water mark. When a new extent is allocated for a table, Oracle dedicates the data blocks to the table, but only raises the high water mark for the table to include the first five data blocks of the new extent. Let us take a look at an example. Assume that we have a new table called CUSTOMER with an initial extent of 50 megabytes and we have added ten rows to this table.

A high water mark is the set of blocks that have at one point contained data. You might have 1000 blocks allocated to a table but only 500 are under the High Water Mark. The blocks under the HWM are the blocks that will be read when the table is fully scanned.
The (HWM) high water mark for an Oracle table is a construct that shows the table at its greatest size. The Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. As a table undergoes deletes and updates, rows shrink and table data blocks become empty. For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 29 seconds to read. After deleting 897,000 rows, a full scan on the table will still take 29 seconds. This is because the table high water mark is not re-set after delete operations.

The issue with the high water mark is that full-table scans will always read up to the high water mark, even though Oracle may be reading through many empty blocks that were 1) allocated to the table, 2) used for rows, and 3) then deleted. Therefore, there are no easy SQL scripts that will reveal the high water mark for an Oracle table, but you can assume that it is the last extent that was allocated to the table for estimation purposes. Here is a simple query to find the high water mark for a table:

select 
   a.tablespace_name, 
   a.file_name, 
   ceil( (nvl(hwm,1)*8192)/1024/1024 ) "Mo" from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
       from dba_extents
       group by file_id
     ) b 
where a.file_id = b.file_id(+)
order by tablespace_name, file_name;

If we did not have a high water mark, a full table scan would read all 50 megabytes of the table looking for new rows. Since all of the rows are below the high water mark, Oracle will only read up to the high water mark and we get our count in less than one second.
There is a danger with this mechanism. Let us use the same example, but this time allocate the customer table, add 100,000 rows and then delete 99,990 rows.
Oracle Water Mark Active
Now, when we execute the statement

 select count(*) from customer;

the query runs for several minutes while all of the data blocks up to the high water mark are accessed.
For highly active tables where large numbers of rows are deleted, you will see very long response times for full table scans. To fix this, you can reorganize the table to lower the high water mark, or you can can force the use of an index, bypassing all full-table scans. The next lesson concludes this module.