Insert Data Is Slower With A Compressed Table In A Not Typical OLTP Environment (Doc ID 1353467.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

If inserting data in a compressed table, then this is slower than when inserting data in a no compressed table.

For example:

---- source table
create table obj
as select * from dba_objects;

---- destination tables
---- no compressed

create table obj_nocomp
as select * from dba_objects where 1=2;

---- compressed

create table obj_comp
compress for all operations
as select * from dba_objects where 1=2;

---- test1

set timing on

---- non compressed table

insert into obj_nocomp
select * from obj;

260547 rows created.

Elapsed: 00:00:01.47

---- compressed table

insert into obj_comp
select * from obj;

260547 rows created.

Elapsed: 00:00:07.38

########################################
-- test 2
-- non compressed table

begin
for i in 1..10000 loop
insert into obj_nocomp
select * from obj where rownum < 100;
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.35

-- compressed table

begin
for i in 1..10000 loop
insert into obj_comp
select * from obj where rownum < 100;
end loop;
end;
/
Elapsed: 00:00:58.61

###########################################
---- test 3

create table obj_small
as select * from dba_objects where rownum < 50;

---- non compressed table

begin
for i in 1..10000 loop
insert into obj_nocomp
select * from obj_small;
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.66

---- compressed table

begin
for i in 1..10000 loop
insert into obj_comp
select * from obj_small;
end loop;
end;
/

Elapsed: 00:00:29.83.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms