Compression preserved on inserts with append hint

I’ve known for a while that if you have an Exadata table compressed with Query High compression and you want to load data into it and have it be compressed as it is loading you have to use direct path loads such as those you get with an insert with an append hint.

I just found out today that this has been true since at least version 9.2 with basic compression.  So, if you have a compressed table and can load it with inserts that have the append hint you won’t need to re-compress the table after the load.

Here is my 9.2 test:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0    Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> 
SQL> drop table test;

Table dropped.

SQL> 
SQL> create table test as select * from dba_tables;

Table created.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        48

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        16

SQL> 
SQL> truncate table test;

Table truncated.

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> insert into test select * from dba_tables;

2740 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        40

SQL> 
SQL> truncate table test;

Table truncated.

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> insert /*+append */ into test select * from dba_tables;

2740 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        16

Uncompressed the table is 48 blocks and if you insert the same rows without the append hint you get 40 blocks which is about the same.

Compressed the table shrinks down to 16 blocks.  If you insert the same data with an append hint you get the same size 16 blocks.  So, the append hint causes the data to be compressed as it is loaded.

Here is a zip of my tests on several versions of Oracle and on Exadata.

– Bobby