Friday, May 7, 2010

Effects of compress option on exp and imp

This is the official description of the compress option from Oracle docs

The default, COMPRESS=y, causes Export to flag table data for consolidation into
one initial extent upon import
If you specify COMPRESS=n, Export uses the current storage parameters, including
the values of initial extent size and next extent size.

What is basically says is that if you set compress=y when imported the table size will be same as the exported size. If you set compress=n then things are different as you can see from below.

create table test1 (a char(2000));

begin
for i in 1 .. 20000
loop
insert into test1 values('a'||i);
end loop;
end;
/

SQL> select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA BYTES
---------- ----------
TEST1 55574528

SQL> delete from test1;

20000 rows deleted.

SQL> select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA BYTES
---------- ----------
TEST1 55574528


Now take two exports one with compress=y and one with compress=n


exp asanga/*** file=test.dmp tables=test1 compress=y
exp asanga/*** file=test_n.dmp tables=test1 compress=n

SQL>drop table test1 purge;

Table dropped.

imp asanga/*** file=test.dmp full=y
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table "TEST1" 0 rows imported
Import terminated successfully without warnings.

SQL>select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA BYTES
---------- ----------
TEST1 55574528

SQL>drop table test1 purge;

Table dropped.

imp asanga/asa file=test_n.dmp full=y

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table "TEST1" 0 rows imported
Import terminated successfully without warnings.

SQL>select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA BYTES
---------- ----------
TEST1 65536


As it could seen from above even if an empty table is imported with the default options, the size of the table doesn't shrink. If compress=n is used the imported table is redcued in size (or number of segments it uses).