Understanding COMPRESS parameter in export

For novices, compress parameter in export has always been a misunderstood parameter. People (in the beginning, as a total novice i too didn’t understand it correctly) relate it with compression of data in the export file or someone would deduce some other definition. So i thought about writing a small post explaining what compress is all about.

First of all from 10g documentation Utilities Guide:

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

In, nutshell what it means to say is that if we specify COMPRESS=y during export then at the time of table creation while importing, the INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.

If we specify COMPRESS=n during export then while creating table in the import, it will use the same values of INITIAL extent as in the original database.

Now lets say I have a table of 100 MB. There have been some deletions and updations and only 50 MB of actual data is there. I export the table with COMPRESS=y and recreate it in some other database. It will sum all the extents and assign as INITIAL extent while creating the table. There is only 50 MB of data in the table but it has allocated 100 MB already. In case, you have limited space this is not a very good option.

If i do with COMPRESS=N and then import the table, its INITIAL extent will be as large as INITIAL extent in the original database and then as required, new extents will be allocated. So now my table in the new database would be approximately 50 MB in size.

Lets try the same with the help of an example of a table.

First of all lets create a table T in schema scott and view the details from various views:

SQL> create table t as select * from all_objects;

Table created.

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                       65536

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0         64
         1         64
         2         64
         3         64
         4         64
         5         64
         6         64
         7         64
         8         64
         9         64
        10         64
        11         64
        12         64
        13         64
        14         64
        15         64
        16       1024
        17       1024
        18       1024
        19       1024
        20       1024
        21       1024

22 rows selected.

SQL>

Now i delete some of the rows from the table and check the size from DBA_SEGMENTS.

SQL> delete t where rownum<31445;

31444 rows deleted.

SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL>

The size is still 7 MB as Oracle has not freed the allocated extents.

Case 1: COMPRESS=y

Now in first case we export the table T with compress=y (which is the default), drop the table and import it back. Then we will check the size of the INITIAL extent, size of the table and information from DBA_SEGMENTS.

$ exp userid=scott/tiger file=T.dmp tables=T

Export: Release 10.2.0.3.0 - Production on Tue Aug 5 15:57:52 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T      31445 rows exported
Export terminated successfully without warnings.
$


SQL> drop table t;

Table dropped.

SQL>


$ imp userid=scott/tiger file=T.dmp tables=T

Import: Release 10.2.0.3.0 - Production on Tue Aug 5 15:59:04 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      31445 rows imported
Import terminated successfully without warnings.
$

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                     7340032

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0       1024
         1       1024
         2       1024
         3       1024
         4       1024
         5       1024
         6       1024

7 rows selected.

SQL>

So here we see the INITIAL extent is of 7340032 bytes (7 MB) which is sum of all the extents allocated to the table originally. Lets now see the actual data in the table. Gather stats on the table and calculate the size of total data from avg_row_len.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" 
2 from dba_tables where table_name='T' and owner='SCOTT';

TABLE_NAME                     size
------------------------------ ------------------------------------------
T                              2825.14kb

SQL>

So the actual data in the table is only 3 MB but its occupying 7 MB. Now lets look at the 2nd case: COMPRESS=n

Case 2: COMPRESS=n

(Note: Here i will recreate the table T from all_objects as when we imported it in last case, it was created with INITIAL extent of 7 MB and it will be useless to experiment on that)

SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> delete t where rownum<31445;

31444 rows deleted.

SQL> commit;

Commit complete.

SQL>

$ exp userid=scott/tiger file=T.dmp tables=T compress=n

Export: Release 10.2.0.3.0 - Production on Tue Aug 5 16:10:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T      63486 rows exported
Export terminated successfully without warnings.
$

SQL> drop table t;

Table dropped.

SQL>

$ imp userid=scott/tiger file=T.dmp tables=T

Import: Release 10.2.0.3.0 - Production on Tue Aug 5 16:12:50 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      63486 rows imported
Import terminated successfully without warnings.
$

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                       65536

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         4

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0         64
         1         64
         2         64
         3         64
         4         64
         5         64
         6         64
         7         64
         8         64
         9         64
        10         64
        11         64
        12         64
        13         64
        14         64
        15         64
        16       1024
        17       1024
        18       1024

19 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
  2  from dba_tables where table_name='T' and owner='SCOTT';

TABLE_NAME                     size
------------------------------ ------------------------------------------
T                              2910.25kb

SQL>

Here we can see the difference. The size of INITIAL extent has not been altered and the space occupied is equal to the total data in the table.

Moral of the story is that if you are using locally managed tablespaces you should always export with COMPRESS=n as allocation of extents is not a big issue here as it was in dictionary managed ones.

Tom Kyte also explains the same in this post on Asktom.

Advertisement