11G
Table compression
User Rating: / 0
- Details
-
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: 3634
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.