The audit table had grown to 200 million rows, so the team finally ran the purge: everything older than 90 days, gone. Ninety-nine percent of the rows deleted, committed, celebrated. The next morning the nightly report that full-scans that table ran exactly as long as it always had. Same I/O, same elapsed time, same line in the AWR report. The data was gone. The work wasn’t.

Nothing is broken here, and the delete did what it was told. The full scan simply doesn’t read rows — it reads blocks, every block up to a boundary called the high-water mark, and DELETE never moves that boundary. This post is what the high-water mark is, why a purge leaves it where it was, how to find tables carrying that dead weight, the fix, and a lab that measures the same scan before the delete, after it, and after the fix.

What the high-water mark is

A table is stored in a segment: a set of blocks the database allocates as the table grows. As rows are inserted, blocks get formatted and filled from the start of the segment outward. The high-water mark (HWM) is the boundary between blocks that have ever been used and blocks that never have.

A full table scan has no index telling it where the rows are, so it reads every block below the high-water mark and looks inside each one. Blocks above the HWM are skipped — they have never held data. Blocks below it are read whether they contain a thousand rows or none.

The optimizer works the same way: it costs a full scan from the table’s block count in its statistics. A table with a high HWM doesn’t just run like a big table, it looks like one to the optimizer too.

Why DELETE doesn’t lower it

DELETE removes rows from blocks. It does not hand the blocks back, and it does not move the high-water mark. The emptied blocks stay part of the segment, below the HWM, marked as having free space so that future inserts can reuse them. That is a sensible design for a table that will grow back — but for a table that was purged and won’t, every full scan now reads thousands of blocks that are empty, or nearly so.

So the table’s cost to scan is set by its largest-ever size, not its current size. A purge that removes 99% of the rows removes 0% of the full-scan work.

⤢ Enlarge

Prove it

The lab loads a 200,000-row table (about 200 bytes a row) and measures one full scan — SELECT /*+ FULL(t) */ COUNT(pad) FROM hwm.t — in consistent gets, the logical block reads the scan performs, taken from V$MYSTAT. Then it deletes 99% of the rows, commits, and runs the same scan:

                       rows   full-scan gets   blocks below HWM
loaded              200,000            6,074              6,175
after DELETE 99%      2,000            6,074              6,175
after SHRINK SPACE    2,000               64                 62

One percent of the rows, one hundred percent of the work — until the high-water mark comes down. After the shrink, the scan’s cost follows the data: 64 gets for 2,000 rows.

Don’t take my word for it — run it. The high-water mark lab loads the table on Oracle Database Free, measures the full scan, deletes 99% of the rows and asserts the scan still costs at least 90% of the baseline, then runs SHRINK SPACE and asserts it drops to 5% or less with every remaining row intact. If the bloat doesn’t reproduce, or the shrink doesn’t fix it, the run fails. Proven on every CI push.

How to find bloated tables

You don’t need to guess. Compare what a table occupies with what its rows actually need, using fresh optimizer statistics in DBA_TABLES:

SELECT owner, table_name, num_rows, blocks,
       CEIL(num_rows * avg_row_len / (8192 * 0.9))                         AS blocks_needed,
       ROUND(100 * (1 - (num_rows * avg_row_len / (8192 * 0.9))
                        / NULLIF(blocks, 0)))                              AS pct_wasted
FROM   dba_tables
WHERE  owner = 'APP'
AND    blocks > 1000
ORDER  BY blocks - CEIL(num_rows * avg_row_len / (8192 * 0.9)) DESC;

blocks is the space below the high-water mark; blocks_needed estimates the space the current rows would fill at about 90% density (adjust 8192 if your block size differs). In the lab, after the delete, this reports 6,175 blocks for rows that need about 58 — 99% wasted. Two caveats: it is only as current as the statistics, so gather first; and it is an estimate, so treat it as a ranking of where to look, not a precise measurement. For precise per-block fullness, DBMS_SPACE.SPACE_USAGE reports how many blocks are empty, 0–25% full, and so on; the database’s automatic Segment Advisor also flags segments with reclaimable space.

Prioritise tables that are both bloated and full-scanned: check AWR or V$SQL_PLAN for TABLE ACCESS FULL on them. A bloated table that is only ever read by primary key costs you disk space, not query time.

The fix

SHRINK SPACE compacts the rows into the start of the segment, lowers the high-water mark and releases the freed space:

ALTER TABLE app.audit_log ENABLE ROW MOVEMENT;
ALTER TABLE app.audit_log SHRINK SPACE;

• It needs row movement, because rows are physically relocated and get new ROWIDs.
• It works on tables in ASSM (automatic segment space management) tablespaces, which is the default.
• It is online: DML continues during the compaction, with only a brief lock at the end to move the HWM.
• Indexes stay usable and are maintained as rows move. Add CASCADE to shrink dependent indexes too.
• SHRINK SPACE COMPACT does the row movement but stops short of moving the HWM, so you can split the work: compact during the day, then run a quick SHRINK SPACE in a quiet window.

ALTER TABLE … MOVE ONLINE (12.2 and later) is the alternative: it rebuilds the table into a new segment, keeps indexes usable, and allows DML throughout, at the cost of needing space for a second copy while it runs. A plain MOVE without ONLINE blocks DML and leaves every index UNUSABLE until rebuilt — a common way to turn a cleanup into an outage. A handful of segment types can’t be shrunk; MOVE ONLINE is the usual fallback.

After either one, gather statistics, so the optimizer sees the new, smaller block count.

What teams get wrong

• Expecting DELETE to make things faster. It reduces rows, not blocks. Full scans, and the optimizer’s costing of them, keep paying for the table’s largest-ever size until the HWM is lowered.
• Running the purge as one giant DELETE every time. For data that ages out by date, partition by date and drop or truncate old partitions instead: it releases the space immediately, generates almost no undo, and lets queries skip old data entirely with partition pruning.
• Using MOVE without ONLINE. Every index on the table goes UNUSABLE, and queries relying on them fail or fall back to full scans until someone rebuilds them.
• Shrinking tables that will grow straight back. Conventional inserts reuse the free space below the HWM. If a table is purged and then refilled on a cycle, shrinking it just makes the next cycle re-allocate the same space. Shrink tables that were purged and will stay small.
• Forgetting direct-path loads. INSERT /*+ APPEND */ always writes above the high-water mark and never reuses free space below it. A table loaded by direct path and purged by DELETE grows forever.
• Skipping the statistics afterwards. Until stats are refreshed, the optimizer still believes the table is thousands of blocks and costs plans accordingly.

Frequently asked questions

What is the high-water mark in Oracle?

The high-water mark (HWM) is the boundary in a table's segment between blocks that have been used to store data at some point and blocks that never have. As rows are inserted, the database formats and fills blocks from the start of the segment outward, pushing the high-water mark up. A full table scan reads every block below the high-water mark, whether those blocks currently contain rows or are empty, and skips the blocks above it. The optimizer also uses the table's block count from statistics, which reflects the space below the high-water mark, to cost full scans.

Why doesn't DELETE make a full table scan faster?

Because DELETE removes rows from blocks but does not release the blocks or lower the high-water mark. The emptied blocks remain part of the table's segment, below the high-water mark, marked as having free space for future inserts. A full table scan reads every block below the high-water mark, so after deleting 99% of the rows it still reads almost exactly the same number of blocks. In the companion lab the same full scan costs 6,074 consistent gets with 200,000 rows and still 6,074 after deleting all but 2,000 of them. Only lowering the high-water mark, with SHRINK SPACE, MOVE, or TRUNCATE, reduces the scan's work.

How do I lower the high-water mark of an Oracle table?

The usual online method is ALTER TABLE ... ENABLE ROW MOVEMENT followed by ALTER TABLE ... SHRINK SPACE, which compacts the rows toward the start of the segment, lowers the high-water mark, and releases the freed space while allowing concurrent DML. It requires an ASSM tablespace, which is the default. Alternatively, ALTER TABLE ... MOVE ONLINE (12.2 and later) rebuilds the table into a new segment while keeping indexes usable. TRUNCATE also resets the high-water mark instantly but removes every row. After any of these, gather optimizer statistics so plans reflect the smaller table.

What is the difference between SHRINK SPACE and SHRINK SPACE COMPACT?

SHRINK SPACE does two phases in one command: it moves rows toward the beginning of the segment, then lowers the high-water mark and releases the space, taking a brief lock at the end to do so. SHRINK SPACE COMPACT performs only the first phase, the row movement, and leaves the high-water mark where it is. That lets you split the work: run the compaction, which can take a while on a large table, during normal hours, and later run a plain SHRINK SPACE in a quiet window, which then completes quickly because the rows are already packed. Adding CASCADE also shrinks the table's dependent indexes.

Does SHRINK SPACE lock the table or invalidate indexes?

SHRINK SPACE is an online operation: queries and DML can continue while rows are being compacted, and only a short exclusive lock is taken at the end to adjust the high-water mark. Indexes remain usable because they are maintained as rows move to new locations. Row movement must be enabled first because rows receive new ROWIDs, so anything that stores ROWIDs long-term would need to tolerate that. This differs from a plain ALTER TABLE ... MOVE without the ONLINE keyword, which blocks DML and marks every index on the table UNUSABLE until it is rebuilt.

How can I find tables with a lot of wasted space below the high-water mark?

After gathering fresh statistics, compare each table's BLOCKS in DBA_TABLES, which is the space below the high-water mark, with the space its rows need, estimated as NUM_ROWS times AVG_ROW_LEN divided by the usable bytes per block (for example 8192 times 0.9). Tables where BLOCKS is far larger than that estimate are carrying dead space; in the companion lab a purged table shows 6,175 blocks against about 58 needed. DBMS_SPACE.SPACE_USAGE gives an exact breakdown of how full the blocks are, and the automatic Segment Advisor reports segments with reclaimable space. Focus on bloated tables that are also read by full scans, since those are where the space turns into query time.

Should I shrink every table after deleting rows?

No. Conventional inserts reuse the free space below the high-water mark, so a table that is regularly purged and then refilled will simply grow back into the same space, and shrinking it only adds work. Shrinking pays off for tables that were purged and will stay much smaller, and that are read by full scans. For data that ages out by date on a schedule, partitioning by date and dropping or truncating old partitions is usually better than repeated DELETE plus SHRINK: it releases space immediately, generates little undo and redo, and lets queries skip old partitions entirely.

High-water mark bloat is the same lesson the rest of the performance series keeps arriving at from different directions: the cost of a query is the work the engine actually does, not the size of the answer. Reading the execution plan shows you a TABLE ACCESS FULL; the right index avoids reading the whole table at all; partition pruning skips the parts you don’t need; and fresh statistics make sure the optimizer is costing the table it actually has. When a purge didn’t make anything faster, check the high-water mark: find the tables whose blocks dwarf their rows, shrink the ones that are full-scanned and won’t grow back, and prove it the way that ends the argument — with the high-water mark lab, where deleting 99% of a table changes nothing until the shrink drops the same scan from 6,074 gets to 64.