Table compression in Oracle 11g

This article presents new features related to table compression in Oracle 11g. It’s not presenting compression related to Exadata.

Compression option is extended in 11g and have following features:

requires compatibility 11.1 for new functionality
extended COMPRESS caluse: COMPRESS [BASIC | FOR OLTP]
BASIC – default, bulk-load operations from prior releases
FOR OLTP – it’s new used for OLTP + direct loads
BASIC and FOR OLTP are not supported for tables with more than 255 columns
only available for heap organized tables
compression can be different for each partition
COMPRESS [BASIC|FOR OLTP] is not used for BasicFile LOBs
new columns in views DBA/ALL/USER_TABLES and DBA/ALL/USER_TAB_PARTITIONS – COMPRESS, COMPRESS_FOR
columns can’t be dropped for compressed tables only set as unused

You can encounter as well following syntax:

COMPRESS FOR DIRECT_LOAD OPERATIONS – it’s equal to COMPRESS, COMPRESS BASIC
COMPRESS FOR ALL OPERATIONS – it’s equal to COMPRESS FOR OLTP
NOCOMPRESS – turns off compression

FOR OLTP algorithm

On the beginning block is empty (Empty Block). When data is inserted into block data is stored in uncompressed format (Initially Uncompressed block). When block is filled based on PCTFREE settings compression is triggered and block has compressed data and free space for another inserts (Compressed Block). So again next loads are loading uncompressed data (Partially Compressed Block) till again block is filled based on PCTFREE which triggers compression (Compressed Block).

NOTE – compression eliminates holes created due to deletions and maximizes contiguous free space.

Examples

Turn on compression for new table. Last table is created as not compressed for later tests.

CREATE TABLE test_tbl_basic COMPRESS
AS
SELECT * FROM dba_objects;

CREATE TABLE test_tbl_oltp COMPRESS FOR OLTP
AS
SELECT * FROM dba_objects;

CREATE TABLE test_tbl_nc NOCOMPRESS
AS
SELECT * FROM dba_objects;

Enable compression for existing table

ALTER TABLE test_tbl_nc COMPRESS FOR OLTP;

Turn off compression for existing table

ALTER TABLE test_tbl_nc NOCOMPRESS;

Checking compression option for table

SELECT table_name, compression, compress_for 
FROM user_tables 
WHERE table_name 
   IN ('TEST_TBL_BASIC', 'TEST_TBL_OLTP', 'TEST_TBL_NC');

TABLE_NAME                     COMPRESSION COMPRESS_FOR
------------------------------ ----------- ------------
TEST_TBL_BASIC                 ENABLED     BASIC        
TEST_TBL_NC                    DISABLED                 
TEST_TBL_OLTP                  ENABLED     OLTP

Specifying compression on partition level

CREATE TABLE test_tbl_part
(id1 NUMBER,
 id2 NUMBER)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10) ,
  PARTITION p2 VALUES LESS THAN(20) NOCOMPRESS,
  PARTITION p3 VALUES LESS THAN(30) COMPRESS,
  PARTITION p4 VALUES LESS THAN(40) COMPRESS BASIC,
  PARTITION p5 VALUES LESS THAN(50) COMPRESS FOR OLTP,
  PARTITION p6 VALUES LESS THAN(60) COMPRESS FOR ALL OPERATIONS
);

SELECT table_name, partition_name, compression, compress_for 
FROM user_tab_partitions
WHERE table_name IN ('TEST_TBL_PART');

TABLE_NAME         PARTITION_NAME     COMPRESSION COMPRESS_FOR
------------------ ------------------ ----------- ------------
TEST_TBL_PART      P1                 DISABLED                 
TEST_TBL_PART      P2                 DISABLED                 
TEST_TBL_PART      P3                 ENABLED     BASIC        
TEST_TBL_PART      P4                 ENABLED     BASIC        
TEST_TBL_PART      P5                 ENABLED     OLTP         
TEST_TBL_PART      P6                 ENABLED     OLTP         

 6 rows selected

Compression advisor DBMS_COMPRESSION

Package DBMS_COMPRESSION has got following procedures:

GET_COMPRESSION_RATIO – analyzes the compression ratio of a table, and gives information about compressibility of a table
GET_COMPRESSION_TYPE – returns the compression type for a specified row

Example analysis of no compressed table for COMPRESS FOR OLTP

set serveroutput on

DECLARE
   blkcnt_cmp        NUMBER;
   blkcnt_uncmp      NUMBER;
   row_perblk_cmp    NUMBER; 
   row_perblk_uncmp  NUMBER;
   cmp_ratio         NUMBER;
   comptype_str      VARCHAR2(60);
BEGIN
  dbms_compression.get_compression_ratio
  (
   'USERS',
   USER,
   'TEST_TBL_NC',
   NULL,
   DBMS_COMPRESSION.COMP_FOR_OLTP,
   blkcnt_cmp,
   blkcnt_uncmp,
   row_perblk_cmp,
   row_perblk_uncmp,
   cmp_ratio,
   comptype_str
  );

  dbms_output.put_line
  (
    'Number of blocks '                 ||chr(10)||
    '  in compressed table:   '         ||blkcnt_cmp||chr(10)||
    '  in uncompressed table: '         ||blkcnt_uncmp||chr(10)||
    'Number of rows per block'          ||chr(10)||
    '  in compressed table:   '         ||row_perblk_cmp||chr(10)||
    '  in uncompressed table: '         ||row_perblk_uncmp||chr(10)||
    'Test done for compression type: '  ||comptype_str||chr(10)||
    'Expected compression ratio: '      ||cmp_ratio||' to 1'
  );
END;
/

anonymous block completed
Number of blocks 
  in compressed table:   344
  in uncompressed table: 1058
Number of rows per block
  in compressed table:   216
  in uncompressed table: 70
Test done for compression type: "Compress For OLTP"
Expected compression ratio: 3 to 1

Example how to check compression type for a row

SELECT 
  dbms_compression.get_compression_type(
   USER, 
   'TEST_TBL_OLTP', 
   ROWID) compression_type
FROM test_tbl_oltp
WHERE ROWNUM <3;

COMPRESSION_TYPE
----------------
               2 
               2

Constants in DBMS_COMPRESSION package

1 – COMP_NOCOMPRESS – no compression

2 – COMP_FOR_OLTP – OLTP compression

4 – COMP_FOR_QUERY_HIGH – exadata high compression for query operations

8 – COMP_FOR_QUERY_LOW – exadata low compression for query operations

16 – COMP_FOR_ARCHIVE_HIGH – exadata high compression for archive operations

32 – COMP_FOR_ARCHIVE_LOW – exadata low compression for archive operations

Have a fun 🙂

Tomasz

DBAORA

oracle expert presents