11G

Table compression

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Sunday, 21 March 2010 19:47
Last Updated on Monday, 12 March 2012 13:54
Published on Sunday, 21 March 2010 19:47
Written by Guy Lambregts
Hits: 3449

Table Compression

Oracle introduced table compression with release 9i R2. But table data only got compressed with direct path inserts. Thus the technology was mainly restricted to Data Warehouses. (Load once and read only afterwards environments).

Oracle 11G has extended the compression technology. It is right now possible to use table compression for any DML operation and as such table compression becomes usable for OLTP environments. (Insert,delete, update all the time environments)

Wat are the added values of table compression ?

1. save disk space

2. save IO during read operations ... since the data is compressed, a select scans less blocks ... there is no uncompression going on. ( nice )

3. save IO while updating and deleting compressed data

Is there any drawback ?

1. there is a penalty for inserts.


Even though the compression occurs at the block level. It does not occur for every insert. Only when certain amount of data — think about a pct used --- is tored in the block the compression occurs.


With 11G table compression we have the choice between

1. no table compression

2. compression for direct path insert ( 9i R2 approach )

3. compression for any operations.


The below DDL examples do not need additional explanation

 



SQL> create table T_COMP_AC ( PK_AC number, AC_SHORT varchar2(20), AC_LONG varchar2(1000)) compress for all operations;

Table created.

SQL> create table T_COMP_DC ( PK_DC number, DC_SHORT varchar2(20), DC_LONG varchar2(1000)) compress for direct_load operations;

Table created.


SQL> create table T_COMP_NC ( PK_NC number, NC_SHORT varchar2(20), NC_LONG varchar2(1000)) nocompress;

Table created.


SQL> select table_name,compression,compress_for from user_tables where table_name like 'T_COMP_%';

TABLE_NAME                     COMPRESS COMPRESS_FOR


T_COMP_AC                      ENABLED  FOR ALL OPERATIONS
T_COMP_DC                      ENABLED  DIRECT LOAD ONLY
T_COMP_NC                      DISABLED



It is possible to have declared the compression at the partition level. take a look to the below example.



SQL> create table TABLE_TRC1 (
2     TRC1_ID NUMBER(18) not null,
3     TRC1_TRANSACTIONDATE  DATE default SYSDATE not null,
4     TRC1_TRANSACTIONVALUE number(10,2) not null,
5     TRC1_ISINCODE varchar2(100) )
6    partition by range (TRC1_TRANSACTIONDATE)
7    ( partition TRC1_P0109 values less than ( TO_DATE('01/02/2009','DD/MM/YYYY') ) compress for all operations,
8      partition TRC1_P0209 values less than ( TO_DATE('01/03/2009','DD/MM/YYYY') ) compress for direct_load operations,
9      partition TRC1_P0309 values less than ( TO_DATE('01/04/2009','DD/MM/YYYY') ) nocompress
10    );

Table created.



SQL>  select partition_name,compression,compress_for from user_tab_partitions where table_name='TABLE_TRC1';

PARTITION_NAME            COMPRESS COMPRESS_FOR


TRC1_P0109                     ENABLED  FOR ALL OPERATIONS
TRC1_P0209                     ENABLED  DIRECT LOAD ONLY
TRC1_P0309                     DISABLED

 

We can alter the compression type with the below listed DDL

 

SQL> alter table T_COMP_AC nocompress;


Table altered.

SQL> alter table T_COMP_AC compress for direct_load operations;

Table altered.

SQL> alter table TABLE_TRC1 modify partition TRC1_P0109 compress for direct_load operations;

Table altered.



is this an interesting new 11G feature ? Yes it is.