Table High water marks

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.

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.