Compression, Advanced Compression & Hybrid Columnar Compression
Version 11.2
 
General
Note: Compression is available for heap tables, partitioned tables (named partitions may be excluded, LOBs, multi-column indexes, and SecureFile tablespaces.

Advanced compression only kicks in if it believes the saving will exceed 20%. This is done to avoid wasting CPU. Be sure to test compressibility before implementing this technology.
Related Data Dictionary Objects
ind$ dba_indexes all_indexes user_indexes
tab$ dba_tables all_tables user_tables
 
Tablespace

Compressed Tablespace
CREATE TABLESPACE ...
DEFAULT COMPRESS;
conn / as sysdba

CREATE TABLESPACE compdef
DATAFILE 'compressed.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT COMPRESS;

CREATE TABLESPACE compall
DATAFILE 'compall.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT COMPRESS FOR ALL OPERATIONS;

CREATE TABLESPACE compdir
DATAFILE 'compdir.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT COMPRESS FOR DIRECT_LOAD OPERATIONS;

SELECT tablespace_name, contents, def_tab_compression, compress_for
FROM dba_tablespaces;
 
TABLESPACE_NAME CONTENTS  DEF_TAB_ COMPRESS_FOR
--------------- --------- -------- ------------------
SYSTEM          PERMANENT DISABLED
SYSAUX          PERMANENT DISABLED
UNDOTBS1        UNDO      DISABLED
TEMP            TEMPORARY DISABLED
USERS           PERMANENT DISABLED
EXAMPLE         PERMANENT DISABLED
UWDATA          PERMANENT DISABLED
COMPALL         PERMANENT ENABLED FOR ALL OPERATIONS
COMPDIR         PERMANENT ENABLED DIRECT LOAD ONLY
COMPDEF         PERMANENT ENABLED DIRECT LOAD ONLY

ALTER USER uwclass QUOTA unlimited ON compdef;
ALTER USER uwclass QUOTA unlimited ON compall;
ALTER USER uwclass QUOTA unlimited ON compdir;
 
Table
The table_compression clause is valid only for heap-organized tables. Use this clause to instruct the database whether to compress data segments to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.

When you enable table compression, Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format. You can specify table compression for the following portions of a heap-organized table.
  • For an entire table, in the physical_properties clause of relational_table or object_table
  • For a range partition, in the table_partition_description of the range_partitioning clause
  • For a list partition, in the table_partition_description of the list_partitioning clause
  • For the storage table of a nested table, in the nested_table_col_properties clause
Table compression saves disk space and reduces memory use in the buffer cache, and is completely transparent to applications. Compression ratios as high as 3.5 : 1 can be achieved. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for DML.

Compressed Tables: Fixed Length Random Data

Note: The "COMPRESS FOR QUERY" and "COMPRESS FOR ARCHIVE" options are only available on Exadata servers.
CREATE TABLE ...
[COMPRESS | COMPRESS FOR OLTP | COMPRESS FOR QUERY | COMPRESS FOR ARCHIVE | COMPRESS FOR DIRECT LOAD OPERATIONS];
conn uwclass/uwclass

CREATE TABLE regreg TABLESPACE uwdata AS
SELECT * FROM all_objects WHERE 1=2;

ALTER TABLE regreg MODIFY owner NULL;
ALTER TABLE regreg MODIFY object_name NULL;
ALTER TABLE regreg MODIFY object_id NULL;
ALTER TABLE regreg MODIFY created NULL;
ALTER TABLE regreg MODIFY last_ddl_time NULL;
ALTER TABLE regreg MODIFY namespace NULL;

CREATE TABLE regdl TABLESPACE uwdata AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE cdreg TABLESPACE compdef AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE cddl TABLESPACE compdef AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE allreg TABLESPACE compall AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE alldl TABLESPACE compall AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE dlreg TABLESPACE compdir AS
SELECT * FROM regreg WHERE 1=2;

CREATE TABLE dldl TABLESPACE compdir AS
SELECT * FROM regreg WHERE 1=2;

DECLARE
 s VARCHAR2(30);
BEGIN
  FOR c IN 1..30000 LOOP
    s := dbms_crypto.randombytes(15);
    INSERT INTO regreg (owner) VALUES (s);  -- uncompressed
  END LOOP;
  COMMIT;

  -- uncompressed with direct load
  INSERT /*+ APPEND */ INTO regdl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- compressed normal insert
  INSERT INTO cdreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- compressed direct load
  INSERT /*+ APPEND */ INTO cddl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for all operations normal insert
  INSERT INTO allreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for all operations direct load
  INSERT /*+ APPEND */ INTO alldl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for direct_load operations normal insert
  INSERT INTO dlreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for direct_load operations direct load
  INSERT /*+ APPEND */ INTO dldl (owner)
  SELECT owner FROM regreg;
  COMMIT;
END;
/

SELECT owner FROM regreg WHERE rownum < 11;

exec dbms_stats.gather_table_stats(USER, 'REGREG');
exec dbms_stats.gather_table_stats(USER, 'REGDL');
exec dbms_stats.gather_table_stats(USER, 'CDREG');
exec dbms_stats.gather_table_stats(USER, 'CDDL');
exec dbms_stats.gather_table_stats(USER, 'ALLREG');
exec dbms_stats.gather_table_stats(USER, 'ALLDL');
exec dbms_stats.gather_table_stats(USER, 'DLREG');
exec dbms_stats.gather_table_stats(USER, 'DLDL');

SELECT table_name, num_rows, blocks, compression, compress_for, pct_free, row_movement
FROM user_tables
WHERE table_name IN ('REGREG','REGDL','CDREG','CDDL','ALLREG','ALLDL','DLREG','DLDL')
ORDER BY 3;

TRUNCATE TABLE regreg;
TRUNCATE TABLE regdl;
TRUNCATE TABLE cdreg;
TRUNCATE TABLE cddl;
TRUNCATE TABLE allreg;
TRUNCATE TABLE alldl;
TRUNCATE TABLE dlreg;
TRUNCATE TABLE dldl;

Compressed Tables: Random Length Random Data
CREATE TABLE ...
[COMPRESS | COMPRESS FOR ALL OPERATIONS | COMPRESS FOR DIRECT LOAD OPERATIONS];
DECLARE
 i INTEGER;
 s VARCHAR2(30);
BEGIN
  FOR c IN 1..30000 LOOP
    i := dbms_random.value(1, 15);
    s := dbms_crypto.randombytes(i);

    INSERT INTO regreg (owner) VALUES (s);  -- uncompressed normal load
  END LOOP;
  COMMIT;

  -- uncompressed with direct load
  INSERT /*+ APPEND */ INTO regdl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- compressed normal load
  INSERT INTO cdreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- compressed direct load
  INSERT /*+ APPEND */ INTO cddl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for all operations normal load
  INSERT INTO allreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for all operations direct load
  INSERT /*+ APPEND */ INTO alldl (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for direct_load operations normal load
  INSERT INTO dlreg (owner)
  SELECT owner FROM regreg;
  COMMIT;

  -- for direct_load operations direct load
  INSERT /*+ APPEND */ INTO dldl (owner)
  SELECT owner FROM regreg;
  COMMIT;
END;
/

SELECT owner FROM regreg WHERE rownum < 11;

exec dbms_stats.gather_table_stats(USER, 'REGREG');
exec dbms_stats.gather_table_stats(USER, 'REGDL');
exec dbms_stats.gather_table_stats(USER, 'CDREG');
exec dbms_stats.gather_table_stats(USER, 'CDDL');
exec dbms_stats.gather_table_stats(USER, 'ALLREG');
exec dbms_stats.gather_table_stats(USER, 'ALLDL');
exec dbms_stats.gather_table_stats(USER, 'DLREG');
exec dbms_stats.gather_table_stats(USER, 'DLDL');

SELECT table_name, num_rows, blocks, compression, compress_for, pct_free, row_movement
FROM user_tables
WHERE table_name IN ('REGREG','REGDL','CDREG','CDDL','ALLREG','ALLDL','DLREG','DLDL')
ORDER BY 3;

TRUNCATE TABLE regreg;
TRUNCATE TABLE regdl;
TRUNCATE TABLE cdreg;
TRUNCATE TABLE cddl;
TRUNCATE TABLE allreg;
TRUNCATE TABLE alldl;
TRUNCATE TABLE dlreg;
TRUNCATE TABLE dldl;

Compressed Tables: Real Data
CREATE TABLE ...
[COMPRESS | COMPRESS FOR ALL OPERATIONS | COMPRESS FOR DIRECT LOAD OPERATIONS];
BEGIN
  -- uncompressed normal load
  INSERT INTO regreg
  SELECT * FROM all_objects;
  COMMIT;

  -- uncompressed with direct load
  INSERT /*+ APPEND */ INTO regdl
  SELECT * FROM all_objects;
  COMMIT;

  -- compressed normal load
  INSERT INTO cdreg
  SELECT * FROM all_objects;
  COMMIT;

  -- compressed direct load
  INSERT /*+ APPEND */ INTO cddl
  SELECT * FROM all_objects;
  COMMIT;

  -- for all operations normal load
  INSERT INTO allreg
  SELECT * FROM all_objects;
  COMMIT;

  -- for all operations direct load
  INSERT /*+ APPEND */ INTO alldl
  SELECT * FROM all_objects;
  COMMIT;

  -- for direct_load operations normal load
  INSERT INTO dlreg
  SELECT * FROM all_objects;
  COMMIT;

  -- for direct_load operations direct load
  INSERT /*+ APPEND */ INTO dldl
  SELECT * FROM all_objects;
  COMMIT;
END;
/

SELECT owner FROM regreg WHERE rownum < 11;

exec dbms_stats.gather_table_stats(USER, 'REGREG');
exec dbms_stats.gather_table_stats(USER, 'REGDL');
exec dbms_stats.gather_table_stats(USER, 'CDREG');
exec dbms_stats.gather_table_stats(USER, 'CDDL');
exec dbms_stats.gather_table_stats(USER, 'ALLREG');
exec dbms_stats.gather_table_stats(USER, 'ALLDL');
exec dbms_stats.gather_table_stats(USER, 'DLREG');
exec dbms_stats.gather_table_stats(USER, 'DLDL');

SELECT table_name, num_rows, blocks, compression, compress_for, pct_free, row_movement
FROM user_tables
WHERE table_name IN ('REGREG','REGDL','CDREG','CDDL','ALLREG','ALLDL','DLREG','DLDL')
ORDER BY 3;

Another example
conn sh/sh

CREATE TABLE sh_regular AS
SELECT * FROM sales;

CREATE TABLE sh_compressed COMPRESS AS
SELECT * FROM sales;

exec dbms_stats.gather_table_stats(USER, 'SALES'); -- partitioned
exec dbms_stats.gather_table_stats(USER, 'SH_COMPRESSED');
exec dbms_stats.gather_table_stats(USER, 'SH_REGULAR');

SELECT table_name, num_rows, blocks, compression
FROM user_tables
WHERE table_name LIKE 'S%';

Columnar Compression
set timing on

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

CREATE TABLE level2
COMPRESS FOR QUERY
AS
SELECT * FROM dba_objects;

CREATE TABLE level3
COMPRESS FOR ARCHIVE
AS
SELECT * FROM dba_objects;

set timing off

exec dbms_stats.gather_table_stats(USER, 'LEVEL1');
exec dbms_stats.gather_table_stats(USER, 'LEVEL2');
exec dbms_stats.gather_table_stats(USER, 'LEVEL3');

col segment_name format a30

SELECT segment_name, segment_type, bytes, blocks, extents
FROM user_segments
WHERE segment_name LIKE 'LEVEL%';

SELECT table_name, status, blocks, empty_blocks, instances, secondary, row_movement, compression, compress_for
FROM user_tables
WHERE table_name LIKE 'LEVEL%';

Columnar Storage Exam
SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM level1;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM level2;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM level3;
 
Indexes

Create Compressed Index
CREATE INDEX <index_name>
ON (<first_column>, <second_column>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
COMPRESS <integer *>;

* Must be at least 1 less than the number of indexed columns
-- For a copy of postal_code visit the Setup Page: [Click Here]

CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata;

ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;

exec dbms_stats.gather_index_stats(USER, 'IX_PCODES');

SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;

SELECT sum(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';

DROP INDEX ix_pcodes;

CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata
COMPRESS 1;

ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;

exec dbms_stats.gather_index_stats(USER, 'IX_PCODES');

SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;

SELECT sum(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';

DROP INDEX ix_pcodes;

CREATE INDEX ix_pcodes
ON postal_code (state_abbrev, city_name, zip_code)
PCTFREE 0
TABLESPACE uwdata
COMPRESS 2;

ANALYZE INDEX ix_pcodes VALIDATE STRUCTURE;

exec dbms_stats.gather_index_stats(USER, 'IX_PCODES');

SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;

SELECT sum(bytes)
FROM user_segments
WHERE segment_name = 'IX_PCODES';

-- so pay attention to opt_comp_count
 
Undo

Undo Segment Compress
Undo segment compression is TRUE by default as of Database 11gR1
set linesize 121
col NAME format a35
col VALUE format a10
col DESCRIPTION format a60

SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
AND x.ksppinm LIKE '%undo%compress%'
ORDER BY 1;

ALTER SYSTEM SET "_undo_block_compression"=FALSE;

SELECT x.ksppinm NAME,
y.ksppstvl VALUE,
ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
AND x.ksppinm LIKE '%undo%compress%'
ORDER BY 1;

ALTER SYSTEM SET "_undo_block_compression"=TRUE;
 
Hybrid Columnar Compression

Compress for Query
CREATE TABLE <table_name> (
<column_data_type_definitions>)
COMPRESS FOR QUERY [LOW | HIGH];
CREATE TABLE hcc_query (
asset_id   VARCHAR2(10),
asset_desc VARCHAR2(30),
trans_date TIMESTAMP)
COMPRESS FOR QUERY LOW;

CREATE TABLE hcc_query (
asset_id   VARCHAR2(10),
asset_desc VARCHAR2(30),
trans_date TIMESTAMP)
COMPRESS FOR QUERY HIGH;

Compress for Archive
CREATE TABLE <table_name> (
<column_data_type_definitions>)
COMPRESS FOR ARCHIVE [LOW | HIGH];
CREATE TABLE hcc_query (
asset_id   VARCHAR2(10),
asset_desc VARCHAR2(30),
trans_date TIMESTAMP)
COMPRESS FOR ARCHIVE LOW;

CREATE TABLE hcc_query (
asset_id   VARCHAR2(10),
asset_desc VARCHAR2(30),
trans_date TIMESTAMP)
COMPRESS FOR ARCHIVE HIGH;
 
Test Cases

Test 1: INSERT Performance
conn uwclass/uwclass

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects
WHERE 1=2;

CREATE TABLE comptab
TABLESPACE uwdata COMPRESS AS
SELECT * FROM all_objects
WHERE 1=2;

SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');

CREATE OR REPLACE PROCEDURE regtest1 IS
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO regtab
    SELECT * FROM all_objects;
  END LOOP;
  COMMIT;
END regtest1;
/

CREATE OR REPLACE PROCEDURE comptest1 IS
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO comptab
    SELECT * FROM all_objects;
  END LOOP;
  COMMIT;
END comptest1;
/

Test 2: UPDATE Performance
SELECT MIN(object_id), MAX(object_id)
FROM all_objects;

CREATE OR REPLACE PROCEDURE regtest2 IS
 j PLS_INTEGER;
BEGIN
  FOR i IN 1..1000 LOOP
    j := TRUNC(dbms_random.value(2, 80984));

    UPDATE regtab
    SET namespace = i
    WHERE object_id = j;
  END LOOP;
  COMMIT;
END regtest2;
/

CREATE OR REPLACE PROCEDURE comptest2 IS
 j PLS_INTEGER;
BEGIN
  FOR i IN 1..1000 LOOP
    j := TRUNC(dbms_random.value(2, 80984));

    UPDATE comptab
    SET namespace = i
    WHERE object_id = j;
  END LOOP;
  COMMIT;
END comptest2;
/

Test 3: DELETE Performance
CREATE OR REPLACE PROCEDURE regtest3 IS
CURSOR s_cur IS
SELECT DISTINCT namespace
FROM regtab;

TYPE NumTab IS TABLE OF regtab.namespace%TYPE INDEX BY PLS_INTEGER;
 s_array NumTab;
BEGIN
  OPEN s_cur;
  FETCH s_cur BULK COLLECT INTO s_array LIMIT 500;

  FOR i IN 1..s_array.LAST LOOP
    DELETE FROM regtab
    WHERE namespace = s_array(i);
  END LOOP;

  CLOSE s_cur;
  COMMIT;
END regtest3;
/

CREATE OR REPLACE PROCEDURE comptest3 IS
CURSOR s_cur IS
SELECT DISTINCT namespace
FROM comptab;

TYPE NumTab IS TABLE OF comptab.namespace%TYPE INDEX BY PLS_INTEGER;
 s_array NumTab;
BEGIN
  OPEN s_cur;
  FETCH s_cur BULK COLLECT INTO s_array LIMIT 500;

  FOR i IN 1..s_array.COUNT LOOP
    DELETE FROM comptab
    WHERE namespace = s_array(i);
  END LOOP;

  CLOSE s_cur;
  COMMIT;
END comptest3;
/

Testing
set timing on
exec regtest1
exec comptest1

 Insert Results
Proc Run 1 Run 2 Run 3
Reg 9.21 9.15 9.31
Comp 9.21 8.92 9.07

exec regtest2
exec comptest2

 Update Results
Proc Run 1 Run 2 Run 3
Reg 21.78 23.21 23.09
Comp 22.56 22.78 23.20

exec regtest3
exec comptest3

 Delete Results
Proc Run 1 Run 2 Run 3
Reg 7.35 8.48 11.81
Comp 8.95 7.68 9.18

TRUNCATE TABLE regtab;
TRUNCATE TABLE comptab;
 
 
Morgan's Library Page Footer
This is site maintained by Dan Morgan. Last Updated: Contact Us Legal Notices & Terms of Use  Privacy Statement