5

I create a table and populate it with data:

create table tab
as
select 'value' || level val
from dual
connect by level <= 10000;

If I then delete all rows and insert the same data:

delete from tab;

insert into tab
select 'value' || level val
from dual
connect by level <= 10000;

commit;

The table size increases every time delete-then-insert is performed. Executing these operation multiple times causes the table to grow to many times it's original size.

I would expect the size to remain (approximately) the same. Why is this happening?

A delete marks space as free, it doesn't actually shrink the segment. A subsequent insert should first use available empty blocks below HWM.

I can recover the space using alter table tab shrink space.

I tested in Oracle 12 and Oracle 18c.

7

If you have access to My Oracle Support:

Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (Doc ID 1601805.1)

So, without just copy-pasting the contents from there, the above note states:

  • the space management algorithm prefers allocating more space over reusing already allocated but free space
  • this is not a bug, but a feature, this is how it was designed
  • you can use shrink to compact the segment, as you have already discovered
|improve this answer|||||
  • Unfortunately, I don't have access to My Oracle Support. Is there a way I can control this behavior? The limitation of manual table shrink is that it cannot be used on compressed tables. – Adamantium Feb 24 at 8:31
  • I ended up with disabling compression and schedling shrinking space periodically. – Adamantium Feb 24 at 11:01
0

Rather than using Delete, use TRUNCATE TABLE command.

|improve this answer|||||
  • I considered that. Unfortunately, I need the delete and insert to execute in one transaction. – Adamantium Feb 24 at 14:17
  • Why are you creating the table each time? It seems that you are creating a table, then deleting it and reinserting the same data. Is that correct? – PV22 Feb 24 at 16:16
  • I encountered this issue in a materialized view that was regularly refreshed (COMPLETE mode). After investigating I noticed that I can get the same effect with delete-then-insert on a regular table (materialized view must be doing that under the hood). – Adamantium Feb 24 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.