Compression

table_compression

The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.
When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table:

• For an entire table, in the physical_properties clause of relational_table or object_table
• For a range partition, in the table_partition_description of the range_partitioning clause
• For a list partition, in the table_partition_description of the list_partitioning clause
• For the storage table of a nested table, in the nested_table_col_properties clause
  
  
  
  Does Oracle datapump uses direct path load?
  
  
  Yes. This is one of feature that makes impdp or expdp more faster than conventional export and import. To use direct path loading through oracle datapump, one has follow certain condition. Alternatively it can can be used by external table method by which we unload the data on flat file on file system of database server and after user can use those flat file as simple data source in its SELECT statement.
  
  
  EXPDP will use DIRECT_PATH mode if:
  
  
  The structure of a table allows a Direct Path unload, i.e.:
  
       - The table does not have fine-grained access control enabled for SELECT.
  
       - The table is not a queue table.
  
       - The table does not contain one or more columns of type BFILE or opaque, or an object type containing opaque columns.
  
       - The table does not contain encrypted columns.
  
       - The table does not contain a column of an evolved type that needs upgrading.
  
       - If the table has a column of datatype LONG or LONG RAW, then this column is the last column. 

Compression in 10g vs shrink and move

1-I wanted to test and see how the compression ratio if the table is shrinked and has no free blocks and the test result was promising as stated below:

SQL> select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
              53.125

Elapsed: 00:00:00.15
SQL> alter table nts_ea3.CUST_CUSTOMER enable row movement;

Table altered.

SQL> alter table nts_ea3.CUST_CUSTOMER shrink space;

Table altered.
SQL> select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
              53.125
SQL> alter table nts_ea3.CUST_CUSTOMER move;

Table altered.

Elapsed: 00:00:03.18
select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
                  54

Elapsed: 00:00:00.08
SQL> alter table nts_ea3.CUST_CUSTOMER move compress;

Table altered.

Elapsed: 00:00:07.12
SQL>  select sum(bytes/1024/1024) from dba_segments where owner='NTS_EA3' and segment_name='CUST_CUSTOMER';

SUM(BYTES/1024/1024)
--------------------
                  28

Elapsed: 00:00:00.16

see how the test result changed from 53M to 28M. i will do the test on a massive amount of data and watch for performance change and i will let you know.

2- Dumpfile Compression

Data Pump Export dump files can contain database metadata objects in a compressed format, allowing dump files to consume less operating system disk space. This metadata is automatically uncompressed during Import.
This feature reduces the amount of disk space consumed by dump files.

Query returns a list of tablespaces that contain XMLTypes:

select distinct p.tablespace_name from dba_tablespaces p, 

  dba_xml_tables x, dba_users u, all_all_tables t where

  t.table_name=x.table_name and t.tablespace_name=p.tablespace_name

  and x.owner=u.username;