Deleting rows changes a table's contents. For an ordinary heap table, it generally leaves the segment's allocated extents and the physical data file's size unchanged. Space made available inside those blocks can support later inserts. Choose a measurement and a reclamation method according to where you need capacity.
Oracle DBA Lesson 45 Part 2 — Deleting Rows and Reusable Space
Follow one deletion
An ordinary nonpartitioned heap table, SPACE_DEMO, has 12 rows, two extents totaling 1,048,576 bytes (1 MiB), and allocations in a 20 MiB physical data file on disk. The file may also contain headers and other allocations. Initial storage allocation or earlier growth can explain a small table occupying a larger segment.
DELETE FROM space_demo WHERE id > 1;
COMMIT;
The predicate keeps the row with identifier 1 and removes the other 11 rows. COMMIT makes the transaction permanent. It does not compact the table, release its extents, lower its high water mark, or resize the data file.
| Measure | Before deletion | After deletion and commit |
|---|---|---|
| Current rows | 12 | 1 |
| Segment allocation, bytes | 1,048,576 | 1,048,576 |
| Segment extents | 2 | 2 |
| Physical data file size | 20 MiB | 20 MiB |
Measure rows and allocation separately
SELECT COUNT(*) AS row_count
FROM space_demo;
SELECT bytes, blocks, extents
FROM user_segments
WHERE segment_name = 'SPACE_DEMO'
AND segment_type = 'TABLE';
COUNT(*) counts current rows visible to the statement. USER_SEGMENTS describes segments owned by the current user. BYTES and BLOCKS measure allocated segment space. EXTENTS counts its extents. They do not report only the current row payload, or the amount of space available for new rows.
Compare the same owner, container, object type, and partition scope. Other changes to the object between the two queries can alter the result.
In an 8 KiB tablespace, 128 allocated blocks equal 1,048,576 bytes. A different allocation policy or initial size can produce different totals. A table with no created segment can have no USER_SEGMENTS row. Index, LOB, and partition segments have their own storage and must be included separately when measuring an object's total footprint.
See USER_SEGMENTS, DBA_SEGMENTS, and CREATE TABLE.
What can reuse the space?
After a deletion commits, another transaction can use eligible space released inside the table's blocks for a conventional insert. The transaction performing the delete can also reuse space after its own freeing statement, before committing. Oracle chooses suitable blocks according to the available space, row size, and segment space management. A particular row-to-block placement is not guaranteed.
A later insert can therefore add rows without requesting another extent. A large row, an unsuitable amount of available space, or a different insertion mode can change that outcome. Direct-path inserts have different placement behavior. Do not generalize the conventional-insert case to every loading operation.
PCTFREE reserves block space for growth of existing rows during updates. It is a block-level storage setting, not a percentage of unused filesystem capacity. Automatic segment space management (ASSM) uses bitmaps to manage block-space availability. Space inside allocated blocks still belongs to this segment. Another table cannot allocate that space as its own extent.
Why the high water mark matters
The high water mark (HWM) bounds the progression of block use within a segment. Beyond it, blocks have never been formatted for use. Ordinary DELETE does not move this boundary backward, even when it removes every row.
In ASSM, some blocks below the HWM can remain unformatted. The low HWM marks the boundary below which all blocks are known to be formatted. A full table scan reads blocks below the low HWM and the formatted blocks between the low HWM and the HWM, using bitmap information to avoid unformatted blocks there. A previously formatted block can be empty after deletion and still contribute to scan work.
A large decrease in row count does not establish a performance problem or predict a matching reduction in reads. Check the actual access path, logical and physical reads, elapsed time, caching, and expected future growth. A small table, or a query using an effective index, may not benefit from a space-reclamation operation.
See logical storage structures.
Choose the capacity you need
| Goal | Relevant capacity | Useful evidence |
|---|---|---|
| More inserts into this table | Eligible space inside its segment | Block-space usage, row sizes, and loading method |
| Allocations for other objects | Free extents in the tablespace | Segment allocation, reclaimed extents, and DBA_FREE_SPACE |
| More filesystem capacity | A smaller physical file | File size, allocation locations, and a safely reducible file tail |
DBMS_SPACE.SPACE_USAGE describes fullness categories for supported ASSM segments. Those categories are not exact live-row payload measurements. DBMS_SPACE.UNUSED_SPACE provides allocation, unused-space, and last-used-block information. Use the documented overload and the supported segment type. Segment Advisor can assess reclaimable space and suggest an appropriate action.
Possible operations have different purposes and consequences:
- Segment shrink. For eligible segments, it compacts rows, adjusts the HWM, and releases reclaimed space. It requires an eligible ASSM tablespace and segment, and row movement. It can change rowids, and it briefly blocks concurrent DML at the final deallocation phase. Review rowid dependencies and other restrictions.
COMPACTalone postpones the HWM adjustment and deallocation. - Deallocate unused space. This releases eligible unused space at the segment's high end. It does not compact holes below the HWM.
- Truncate. This removes all rows and resets the HWM. Its storage clauses determine retained allocation. The default releases eligible storage except the retained minimum.
REUSE STORAGEkeeps allocated space for reuse. Truncate is DDL and cannot be rolled back. - Reorganization or redefinition. This can change placement and recover space, with operation-specific privileges, extra space, availability, and dependency considerations.
Releasing extents makes them available within the tablespace. A physical file keeps its size until a separate resize succeeds. Free extents scattered through a file do not prove that its end can be removed. Allocated blocks near the requested new end can prevent the resize. Use file-layout and file-header or minimum-size information rather than subtracting total free bytes from the file size. Filesystem allocation, thin provisioning, and underlying storage accounting can add further differences.
See managing space for schema objects, TRUNCATE TABLE, changing data file size, DBA_FREE_SPACE, and DBMS_SPACE.
Recap
DELETE and COMMIT on an ordinary heap table reduce the row count and can leave eligible space inside existing blocks. They do not, by themselves, release extents, lower the high water mark, or shrink the data file. Measure rows with COUNT(*) and allocation with USER_SEGMENTS. Include index, LOB, and partition segments when the total footprint matters. A conventional insert can reuse suitable space in the allocated blocks. A full table scan can still read formatted empty blocks below the HWM. Match the reclamation method to the capacity you need: shrink, deallocate unused space, truncate, or reorganize. Releasing extents does not by itself shrink the data file.
No comments:
Post a Comment