Table High Water Marks and Full-Table Scans

When an Oracle table is created, the STORAGE clause determines the initial and next extent sizes. Oracle allocates data blocks within the tablespace based on these parameters. To prevent the database from reading into storage that has never contained rows, Oracle maintains a per-table high water mark (HWM) — a boundary that tells the full-table scan engine exactly how far into the segment it needs to read.

When a new extent is allocated to a table, Oracle does not immediately raise the HWM to cover the entire extent. Instead, the HWM advances in five-block increments as INSERT activity demands more formatted space. To illustrate: assume a new CUSTOMER table with a 50MB initial extent and ten rows inserted. The HWM sits just past the blocks containing those ten rows. The remaining approximately 49.9MB of the extent is allocated to the table but sits above the HWM and is never read during a full-table scan.

The HWM is the set of blocks that have at some point contained data. Of 1,000 blocks allocated to a table, only those below the HWM are read during a full-table scan. Blocks above the HWM have never been formatted and are skipped entirely.

The HWM represents the table at its historical greatest size — the point at which it consumed the most extents. As rows are deleted, data blocks become empty, but the HWM does not retreat. Oracle retains the HWM position rather than recalculating it after each DELETE, because scanning tail extents to find the true last occupied block would be prohibitively expensive on large segments.

The practical consequence is significant: a one-million-row table that takes 29 seconds to full-scan will still take 29 seconds after 897,000 rows are deleted. The HWM has not moved; the scan depth is unchanged.

Estimating the High Water Mark

There is no single V$ view that directly exposes the per-table HWM as a block address. A datafile-level approximation can be derived from DBA_EXTENTS using the highest allocated block address per file:

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;

For a more precise per-table HWM measurement in Oracle 10g and later, use DBMS_SPACE.SPACE_USAGE. The unformatted_blocks output parameter reports the number of blocks above the HWM that have been allocated to the segment but never written to:

DECLARE
  l_fs1_bytes          NUMBER;
  l_fs2_bytes          NUMBER;
  l_fs3_bytes          NUMBER;
  l_fs4_bytes          NUMBER;
  l_full_bytes         NUMBER;
  l_unformatted_bytes  NUMBER;
  l_unformatted_blocks NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE(
    segment_owner      => 'SCHEMA_NAME',
    segment_name       => 'CUSTOMER',
    segment_type       => 'TABLE',
    fs1_bytes          => l_fs1_bytes,
    fs2_bytes          => l_fs2_bytes,
    fs3_bytes          => l_fs3_bytes,
    fs4_bytes          => l_fs4_bytes,
    full_bytes         => l_full_bytes,
    unformatted_bytes  => l_unformatted_bytes,
    unformatted_blocks => l_unformatted_blocks
  );
  DBMS_OUTPUT.PUT_LINE('Unformatted blocks above HWM: ' ||
    l_unformatted_blocks);
END;
/

Without a high water mark, a full-table scan on the 50MB CUSTOMER table would read all 50MB regardless of how many rows exist. The HWM prevents this by bounding the scan to only the blocks that have ever held data. With ten rows inserted, the scan stops after a handful of blocks — returning the count in under a second.

The danger emerges when large DELETE operations follow a period of heavy INSERT activity. Consider the CUSTOMER table loaded with 100,000 rows and then 99,990 rows deleted. The HWM sits at the high point reached during the INSERT phase. Executing:

SELECT count(*) FROM customer;

now causes Oracle to read all blocks up to the HWM — including the thousands of empty blocks left by the deleted rows — and the query runs for several minutes. This is one of the most common causes of unexplained performance degradation on tables that appear small by row count but remain large by HWM position.

Only the following operations reset or lower the HWM:

1. TRUNCATE TABLE — removes all rows and resets the HWM to zero immediately. No rollback is possible.
2. ALTER TABLE ... MOVE — rebuilds the segment with a fresh HWM. All dependent indexes become unusable and must be rebuilt with ALTER INDEX ... REBUILD.
3. ALTER TABLE ... SHRINK SPACE — compacts row data and lowers the HWM in-place. Requires the tablespace to use ASSM and row movement to be enabled on the table (ALTER TABLE ... ENABLE ROW MOVEMENT).
4. CTAS and rename — CREATE TABLE new_table AS SELECT * FROM old_table builds a fresh segment from scratch, followed by a rename and index rebuild. Suitable for tables that can tolerate a maintenance window.

To fix a high-HWM performance problem without taking the table offline, use DBMS_REDEFINITION to rebuild online, or force an index hint to bypass full-table scans while reorganization is scheduled:

-- Force index use to bypass the full-table scan while HWM is high
SELECT /*+ INDEX(customer customer_pk) */ count(*)
FROM   customer;

The APPEND hint converts a conventional INSERT — which acquires free blocks from the freelist below the HWM — into a direct-path INSERT that writes new data directly above the HWM, bypassing the buffer cache entirely. This is the fastest available method for bulk-loading rows into a table because it eliminates buffer cache overhead and, when the tablespace runs in NOLOGGING mode, minimizes redo generation.

Key behaviors of direct-path INSERT:

• Data is written to new blocks above the HWM, not to free space within existing allocated blocks.
• The APPEND and APPEND_VALUES hints both convert conventional inserts to direct-path inserts automatically.
• When parallel DML is active, direct-path mode is the default. Use the NOAPPEND hint to force conventional INSERT mode when direct-path is undesirable.
• Only one concurrent APPEND operation can run per non-partitioned table at a time. Two simultaneous APPEND inserts on the same table contend for an exclusive segment enqueue, degrading performance to serial execution.
• For partitioned tables, concurrent APPEND operations are safe as long as each targets a separate partition.
• After a direct-path INSERT, the table is locked for further DML until the transaction is committed or rolled back. Always COMMIT immediately after the APPEND to release the lock.

-- Bulk direct-path INSERT using APPEND hint
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;
COMMIT;

-- Direct-path INSERT for a single row using APPEND_VALUES
INSERT /*+ APPEND_VALUES */ INTO orders
  (order_id, customer_id, order_date)
VALUES (seq_orders.NEXTVAL, 1001, SYSDATE);
COMMIT;

The HWM concept and full-table scan behavior are unchanged in Oracle 23ai. The per-table HWM is documented without modification in the Oracle 23ai Logical Storage Structures and Managing Tables chapters. Specific 23ai context worth noting:

• Tablespace Shrink (enhanced in 23ai) reclaims space at the datafile level but does not alter individual table HWMs.
• Automatic Indexing (from Oracle 19c, continued in 23ai) reduces the frequency of full-table scans by promoting beneficial indexes automatically, but does not change HWM mechanics.
• In-Memory Column Store can serve analytical queries from memory without performing a block-level full-table scan, but the underlying HWM for the heap table is unchanged.
• AI Vector Search introduces vector indexes for semantic similarity queries; standard B-tree and full-table scan behavior and HWM interaction are unaffected.

The classic remedies remain correct in 23ai: TRUNCATE, ALTER TABLE MOVE, ALTER TABLE SHRINK SPACE, and CTAS are the four mechanisms that reset the HWM.

The high water mark is a segment-level boundary that bounds full-table scan depth to blocks that have historically held data. It advances in five-block increments as INSERT activity fills new blocks, and never retreats automatically when rows are deleted. A table that once held a million rows and now holds ten thousand may still scan at million-row cost if the HWM has not been reset.

The APPEND hint exploits the HWM by writing new data directly above it, delivering the fastest available bulk-insert path at the cost of serialized table access and an immediate COMMIT requirement.

For highly active tables where large DELETE volumes accumulate, resetting the HWM via TRUNCATE, MOVE, SHRINK SPACE, or CTAS is one of the highest-impact and most underused DBA performance optimizations available. The next lesson concludes this module.