CODEWITHSUNDEEP
oracle-databaseoracle11g
griboedov
griboedov

Reputation: 886

Reusing space after deleting rows in Oracle

In Oracle the space allocated for data after INSERT INTO operation is not cleaned up when deleting rows from the table. Instead, after DELETE FROM operation some "waste space" is left. So what happens when I do INSERT INTO after DELETE FROM - does it span this "waste space" or allocates new space again?

Upvotes: 2

Views: 3089

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

It is the high water mark (HWM) concept.

high water mark (HWM)

The boundary between used and unused space in a segment.

Very well explained in detail by Thomas Kyte here.

Standard Oracle table is a heap-organized table. It is a table with rows stored in no particular order.

If you want to claim the free space, then you need to reorganize the table.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132670

See the Oracle Concepts manual:

By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

Upvotes: 2

Related Questions