Oracle 21c — Setting Compression Attribute for Indexes During Import

In Oracle 12cR1, a new feature was introduced that allowed us to enable or disable the compression property for tables during a dump import. This was done using the TRANSFORM parameter in the impdp command:

SQL*Plus: Release 12.2.0.1.0 Production 

SQL> select compression from   dba_tables where  table_name = 'TBL1';
COMPRESS
--------
DISABLED

SQL> drop table usef.tbl1;
Table dropped.

[oracle@oshost ~]$ impdp usef/a directory=drm dumpfile=test.dmp tables=usef.tbl1 TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\"

SQL> select compression,COMPRESS_FOR from dba_tables where  table_name = 'TBL1';
COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

This feature was only available at the table level, and there was no way to control index compression during import (in Oracle 12c).

However, in Oracle 21c, as a new feature, you can now enable (or disable) the compression attribute at the index level during import:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select index_name,compression from user_indexes where  table_name = 'TBL1';
INDEX_NAME COMPRESSION
---------- -------------
IND1       DISABLED

SQL> drop table tbl1;
Table dropped.

[oracle@oLinux7 ~]$ impdp  usef/a@192.168.1.20:1521/pdb1 directory=drm dumpfile=test.dmp tables=usef.tbl1 transform=index_compression_clause:\"compress advanced low\"


SQL*Plus: Release 21.0.0.0.0 - Production 
SQL> select index_name,compression from user_indexes where  table_name = 'TBL1';
INDEX_NAME COMPRESSION
---------- -------------
IND1       ADVANCED LOW

The reverse is also true: if compression is already enabled for an index, you can disable it during import:

 SQL> select index_name,compression from user_indexes where  table_name = 'TBL1';
INDEX_NAME COMPRESSION
---------- -------------
IND1       ADVANCED LOW

impdp  usef/a@192.168.1.20:1521/pdb1 directory=drm dumpfile=test.dmp tables=usef.tbl1 transform=index_compression_cl ause:\"nocompress\"

SQL> select index_name,compression from user_indexes where  table_name = 'TBL1';
INDEX_NAME COMPRESSION
---------- -------------
IND1       DISABLED