Compress table

Compress table
  • 6
    Dic

Compress table

twittergoogle_pluslinkedin

Scenery: Mass distribution of rich and multimedia content over the Internet, made possible through

advancements in broadband technologies, also contributes to the growth in overall data volume.

The enormous growth in the volume of data that needs to be retained online makes storage one of the

biggest cost elements of most IT budgets.

The compression clause can be specified at the tablespace, table or partition level with the following

options:

NOCOMPRESS – The table or partition is not compressed. This is the default action

COMPRESS – This option is considered suitable for data warehouse systems

OMPRESS FOR ALL OPERATIONS – This option is considered suitable for OLTP systems. As the name implies,the option enables compression for

all operations, including DML statements. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been

deprecated.

ie for tablespace:

. CREATE TABLESPACE etl_test

DATAFILE '/u01/app/oracle/oradata/DB11G/produzione.dbf'

SIZE 100M

DEFAULT COMPRESS FOR ALL OPERATIONS ;

If you want to see option table just query the dictionary view dba_tablespaces as the following statement:

SELECT def_tab_compression, compress_for FROM dba_tablespaces WHERE tablespace_name = 'etl_test';

. ALTER TABLESPACE etl_test DEFAULT NOCOMPRESS;

SELECT def_tab_compression, compress_for FROM dba_tablespaces WHERE tablespace_name = 'etl_test';

. DROP TABLESPACE etl_test INCLUDING CONTENTS AND DATAFILES;

ie for table:

CREATE TABLE ETL.DWH_CURRENCY

(

 dte_yer_mth DATE not null,

 cod_curr    VARCHAR2(3) not null,

 val_rate    NUMBER,

 dsc_curr    VARCHAR2(50)

)

tablespace produzione

compress;

If you want to drop columns from compress table, take action to set UNUSED the column that wants to drop from table, ie:

ALTER TABLE ETL.DWH_CURRENCY UNUSED (cod_curr);

ALTER TABLE ETL.DWH_CURRENCY DROP unused columns;

If you want to see option table just query the dictionary view user_table as the following statement:

SELECT table_name, compression, compress_for FROM user_tables;

ie for partition:

CREATE TABLE an_customer (

id NUMBER(10),

name VARCHAR2(50),

cod VARCHAR2(20),

dta_in DATE)

TABLESPACE produzione

PARTITION BY RANGE (dta_in) (

PARTITION an_customer1 VALUES LESS THAN (TO_DATE('20150101', 'YYYYMMDD')) COMPRESS,

PARTITION an_customer2 VALUES LESS THAN (TO_DATE('20150601', 'YYYYMMDD')) COMPRESS FOR ALL

OPERATIONS,

PARTITION an_customer3 VALUES LESS THAN (MAXVALUE) NOCOMPRESS

);

If you want to see option table just query the dictionary view user_tab_partitions as the following statement:

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;

Around the scenery compress table:

– Compression is not applied to lob segments

– Table compression is only valid for heap organized tables, not index organized tables.

– Table compression cannot be specified for external or clustered tables

Excursus:

– Compression for OLTP

– Compression for File Data

– Compression for Backup Data

– Compression for Network Traffic

http://www.oracle.com/technetwork/database/focus-areas/storage/advanced-compression-whitepaper-130502.pdf