Infrastructure at your Service

Daniel Westermann

does impdb into a compressed table really compress data?

Today at a customer we discussed the following scenario: To refresh a test database a datapump export and import was implemented. To save space on the test system the idea came up to compress the data on the test system. When we checked the documentation we came across the following statement:

“When you use basic table compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.”

So the question was: Is datapump loading data in bulk in the sense of the sentence above? Needs to be tested:

connect / as sysdba
drop user demo cascade;
drop user demo2 cascade;
create or replace directory dir_tmp as '/var/tmp';
create user demo identified by demo default tablespace users;
create user demo2 identified by demo default tablespace users;
grant dba to demo;
grant dba to demo2;
create table demo.DEMO ("id" number primary key, "text" varchar2(15), "number" number) ;
create table demo2.demo compress basic as select * from demo.demo;
set timing on
 declare
  type people_array is varray(12) of varchar(15);
  people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
  people_count number :=people.COUNT;
  type DEMO_record_type is record ("user_id" number , "name" varchar2(15), "number" number) ;
  type DEMO_collection_type is table of DEMO_record_type index by binary_integer;
  DEMO_collection DEMO_collection_type;
 begin
  -- fill the collection
  for i in 1..1e6 loop
   DEMO_collection(i)."user_id":=i;
   DEMO_collection(i)."name":=people( dbms_random.value(1,people.count) );
   DEMO_collection(i)."number":=trunc(dbms_random.value(0,10000));
   null;
  end loop;
  forall i in 1..DEMO_collection.count insert /*+ append_values */ into demo.DEMO values DEMO_collection(i);
  commit;
 end;
 /
select sum(bytes)/1024/1024 mb_used from dba_extents where segment_name='DEMO' and owner='DEMO';
!rm -f /var/tmp/demo.*
!expdp userid="'/ as sysdba'" schemas=demo directory=dir_tmp dumpfile=demo.dmp logfile=demo.log
!impdp userid="'/ as sysdba'" directory=dir_tmp dumpfile=demo.dmp logfile=demo.log remap_schema=demo:demo2 TABLE_EXISTS_ACTION=APPEND
select sum(bytes)/1024/1024 mb_used from dba_extents where segment_name='DEMO' and owner='DEMO2';

The important output of the script for the uncompressed table is:

MB_USED
 ----------
 22

.. and for the compressed table:

MB_USED
 ----------
 16
 So, the answer is: Yes.

Btw: In 12c there is a transform switch to enable table compression without pre-creating the table:

impdp userid="'/ as sysdba'" directory=dir_tmp dumpfile=demo.dmp logfile=demo.log remap_schema=demo:demo2 TRANSFORM=TABLE_COMPRESSION_CLAUSE:COMPRESS

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure