Different types of Oracle Database compression in the same data segment

Hi there!


Do you know that Oracle Database could store blocks with different type of compression in the same segment? (unpartitioned table or partition/subpartition)

Simple example using Exadata.

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 19 17:37:41 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 500
SQL> create table vd.test_compress as select * from dba_objects;

Table created.

SQL> alter table vd.test_compress compress for OLTP;

Table altered.

SQL> insert /*+ APPEND */ into vd.test_compress select * from dba_objects;

647847 rows created.

SQL> alter table vd.test_compress compress for QUERY LOW;

Table altered.

SQL> insert /*+ APPEND */ into vd.test_compress select * from dba_objects;

647850 rows created.

SQL> alter table vd.test_compress compress for QUERY HIGH;

Table altered.

SQL> insert /*+ APPEND */ into vd.test_compress select * from dba_objects;

647848 rows created.

SQL> alter table vd.test_compress compress for ARCHIVE LOW;

Table altered.

SQL> insert /*+ APPEND */ into vd.test_compress select * from dba_objects;

647847 rows created.

SQL> alter table vd.test_compress compress for ARCHIVE HIGH;

Table altered.

SQL> insert /*+ APPEND */ into vd.test_compress select * from dba_objects;

647851 rows created.

SQL> commit;

Commit complete.

SQL> select
  2  case DBMS_COMPRESSION.GET_COMPRESSION_TYPE ('VD', 'TEST_COMPRESS', ROWID)
  3  when 1 then '1 - No Compresson'
  4  when 2 then '2 - OLTP'
  5  when 4 then '4 - Query High'
  6  when 8 then '3 - Query Low'
  7  when 16 then '6 - Archive High'
  8  when 32 then '5 - Archive Low'
  9  END "Compression Type",
 10  count( distinct DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID, 'SMALLFILE')) blocks
 11  from vd.test_compress
 12  group by DBMS_COMPRESSION.GET_COMPRESSION_TYPE ('VD', 'TEST_COMPRESS', ROWID)
 13  order by 1;

Compression Type      BLOCKS
----------------- ----------
1 - No Compresson       2653
2 - OLTP                 700
3 - Query Low            232
4 - Query High            35
5 - Archive Low           35
6 - Archive High          21

6 rows selected.