A Complete Explanation of Segment Shrink vs. DELETE/TRUNCATE Behavior

Efficient operation of Oracle Database requires freeing unnecessary space and optimizing performance. At the core of this is Segment Shrink. To use Segment Shrink effectively, you must correctly understand the differences in how data deletion methods (DELETE/TRUNCATE) behave.

This article explains the following in detail:

Differences between DELETE and TRUNCATE (with diagrams for beginners)
How Segment Shrink works (visualized for understanding)
Execution procedures and precautions (with concrete command examples)
Practical tips and best practices
How to verify the effect (with SQL scripts)

1. Detailed explanation of the differences between DELETE and TRUNCATE

There are two primary ways to delete data: DELETE and TRUNCATE. This section explains their characteristics and behavioral differences.

What is DELETE?

Partial deletion is possible: You can delete only the matching rows using the WHERE clause.
Uses the UNDO segment: Deleted data is recorded in the UNDO segment, allowing rollback.
High Water Mark (HWM) remains: Although freed, blocks stay unused; HWM is not reset, so the segment size does not change.

What is TRUNCATE?

Fast and efficient full deletion: Instantly removes all table data.
Does not use the UNDO segment: It does not record deletion logs, so rollback is not possible.
HWM is reset: All table blocks are released, and the segment size is initialized.

Understand DELETE vs. TRUNCATE with a diagram

+----------------+--------------------------+----------------------------+
| Operation      | DELETE                   | TRUNCATE                   |
+----------------+--------------------------+----------------------------+
| Condition use  | Partial delete via WHERE | Not allowed (full delete)  |
| Rollback       | Possible                 | Not possible               |
| High Water Mark| No impact (unchanged)    | Reset                      |
| Execution speed| Slow (logging)           | Very fast (no logging)     |
| UNDO usage     | Uses UNDO                | Does not use UNDO          |
| Space release  | Not released (needs shrink)| Immediately released     |
+----------------+--------------------------+----------------------------+

2. Visual explanation of how Segment Shrink works

What is the High Water Mark (HWM)?

The High Water Mark indicates the end of used blocks in a table/segment. Space beyond this mark remains unused, and even after deletion it is not shrunk automatically.

Segment state after DELETE

[After DELETE]
+---------+---------+---------+---------+---------+
| Data    | Deleted | Data    | Deleted | HWM     |
+---------+---------+---------+---------+---------+

Although data is deleted, the blocks remain unused.
HWM does not go down unless you run Segment Shrink.

Segment state after TRUNCATE

[After TRUNCATE]
+---------+---------+---------+---------+---------+
| Only empty blocks                         | HWM |
+---------+---------+---------+---------+---------+

TRUNCATE resets the HWM and immediately releases unused blocks.

3. Execution procedures and precautions for Segment Shrink

Execution procedures

Enable ROW MOVEMENT
ALTER TABLE <table_name> ENABLE ROW MOVEMENT;
Execute Segment Shrink
ALTER TABLE <table_name> SHRINK SPACE;
Partial shrink (compact only)
ALTER TABLE <table_name> SHRINK SPACE COMPACT;
Shrink an index
ALTER INDEX <index_name> SHRINK SPACE;

Precautions

Ensure sufficient UNDO: Shrink operations can temporarily consume a large amount of UNDO.
Performance impact: Load increases during shrink operations; avoid peak hours.
Online shrink: SHRINK SPACE allows reads/writes, but it may affect certain transactions.

4. Tips useful in real operations

Tip 1: Combine DELETE with shrink

When you use DELETE, always perform shrink to free unused space.

Tip 2: Leverage TRUNCATE

For mass deletions, TRUNCATE minimizes logging and speeds up the operation.

Tip 3: Check ARCHIVELOG mode

A large amount of REDO can be generated during shrink. Confirm your ARCHIVELOG settings.

5. How to verify the effect of Segment Shrink

After shrink, use the following SQL to verify its effect.

Check the High Water Mark

ANALYZE TABLE <table_name> VALIDATE STRUCTURE;
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE
FROM USER_TABLES
WHERE TABLE_NAME = '<table_name>';

Check segment size

SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = '<table_name>';

Example comparison before/after shrink

Before: 100MB
After :  40MB
Effect: 60MB space reduction

6. Concrete scenarios and application examples

Scenario 1: Deleting old data

Delete sales data older than five years.
DELETE FROM sales WHERE transaction_date < TO_DATE('2019-01-01', 'YYYY-MM-DD');
Perform Segment Shrink.
ALTER TABLE sales SHRINK SPACE;

Scenario 2: Regular maintenance

Perform shrink after monthly batch processing to reclaim unused space.

Summary

Choosing between DELETE and TRUNCATE

DELETE: When you need partial data deletion.
TRUNCATE: When you want to delete all data quickly.

Importance of Segment Shrink

Lowers the High Water Mark after DELETE and optimizes disk space.
Contributes to improved performance and efficient resource usage.

Understand the correct use of Segment Shrink and DELETE/TRUNCATE, and apply it to operations!

[Reference]
Oracle Database Database Performance Tuning Guide, 19c