11g Compression and Segment Space Reuse

An interesting behavior could be observed when rows are deleted and re-inserted to compressed and uncompressed table.

First the uncompressed table.

create table insertdml as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);

Use the pl/sql code sinppet to find the space usage.

full_blocks 0
full_bytes 0

total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416

Add rows to the table to expand it

declare

type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;

forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/

Check the space usage after the insert

full_blocks 1002
full_bytes 8208384

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096

Note the last used extent block id and delete the rows from the table and check the space usage

delete from insertdml;
commit;

full_blocks 0
full_bytes 0

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096

No more full blocks. Again insert the rows and check the space usage

full_blocks 999
full_bytes 8183808

total_blocks 1152
total_bytes 9437184
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 36096

Total number of blocks hasn't changed and last used extent block is still the same which means no new extents were added to the segment and old extents are reused.

Now the compressed table

drop table insertdml purge;
create table insertdml compress for all operations as select * from all_objects where 1 = 2;
alter table insertdml add constraint insert_pk primary key (object_id);

check the initial space usage as before

full_blocks 0
full_bytes 0

total_blocks 8
total_bytes 65536
unused_blocks 5
unused_bytes 40960
last_used_extent_file_id 6
last_used_extent_block_id 34416

insert rows to extend the table

declare

type insertrow_type is table of all_objects%rowtype index by pls_integer;
insertrow insertrow_type;
begin
select * bulk collect into insertrow from all_objects;

forall row in insertrow.first .. insertrow.last
insert into insertdml values insertrow(row);
end;
/

and check the space usage

full_blocks 401
full_bytes 3284992

total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584

delete rows and check the space usage again

delete from insertdml;
commit;

full_blocks 0
full_bytes 0

total_blocks 640
total_bytes 5242880
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35584

insert rows again and check the space usage

full_blocks 645
full_bytes 5283840

total_blocks 768
total_bytes 6291456
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 6
last_used_extent_block_id 35712

Total number of blocks has changed and also the last used extent block id which indicates new extents are being added instead of old extents being reused.

Instead of the pl/sql code sinppet quering the user_segments view would also reveal this fact

select segment_name,bytes from user_segments where segment_name='INSERTDML';

This behavior was not observed in Oracle 11g R2.

--after initial insert

full_blocks 326
full_bytes 2670592

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296

--delete the rows

full_blocks 0
full_bytes 0

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296


--insert again

full_blocks 327
full_bytes 2678784

total_blocks 512
total_bytes 4194304
unused_blocks 0
unused_bytes 0
last_used_extent_file_id 4
last_used_extent_block_id 135296

Last used extent block id hasn't changed.