Compress table

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

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *