Oracle Basic, Prefix, Advanced, Hybrid Columnar,  In-Memory & Undo Compression
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
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.

The Advanced Compression license in 12cR1 includes not just block compression but also unstructured data (files), network transmissions, and backups.

Exadata Note: There are three technologies critical to understand with Exadata. SmartScan, Hybrid Column Compression (HCC), and Storage Indexes. HCC is one of the three pillars.
Data Dictionary Objects
ALL_INDEXES DBA_INDEXES TAB$
ALL_TABLES DBA_TABLES USER_INDEXES
CDB_INDEXES DBMS_COMPRESSION USER_TABLES
CDB_TABLES IND$  
 
Tablespace Compression
Generic Compressed Tablespace

These syntaxes are valid in 12.2 but appear to be deprecated
CREATE TABLESPACE <tablespace_name>
DATAFILE <datafile_name> SIZE <integer>
... other tablespace options ...
DEFAULT [TABLE] COMPRESS [<FOR ALL OPERATIONS | FOR DIRECT_LOAD OPERATIONS>];
conn sys@pdbdev 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, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE

SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED
Advanced OLTP Compression for Table DEFAULT [TABLE] COMPRESS FOR OLTP
conn sys@pdbdev as sysdba

CREATE TABLESPACE compoltp
DATAFILE 'compoltp.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT COMPRESS FOR OLTP;

SELECT tablespace_name, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE
COMPOLTP         ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED
Advanced Query Compression for Table DEFAULT [TABLE] COMPRESS FOR QUERY <LOW | HIGH>
conn sys@pdbdev as sysdba

CREATE TABLESPACE compquery
DATAFILE 'compquery.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT TABLE COMPRESS FOR QUERY HIGH;
CREATE TABLESPACE compquery
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
Advanced Archive Compression for Table DEFAULT [TABLE] COMPRESS FOR ARCHIVE <LOW | HIGH>
conn sys@pdbdev as sysdba

CREATE TABLESPACE comparch
DATAFILE 'comparch.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT TABLE COMPRESS FOR QUERY LOW;
CREATE TABLESPACE comparch
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
Advanced Compression for Index DEFAULT INDEX COMPRESS ADVANCED <LOW | HIGH>
conn sys@pdbdev as sysdba

CREATE TABLESPACE compidxadv
DATAFILE 'compidxadv.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT INDEX COMPRESS ADVANCED HIGH;

SELECT tablespace_name, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE
COMPIDXADV       DISABLED                                ENABLED      ADVANCED HIGH
COMPOLTP         ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED
In-Memory Compression for DML DEFAULT INMEMORY MEMCOMPRESS FOR DML
conn sys@pdbdev as sysdba

CREATE TABLESPACE compmemdml
DATAFILE 'compmemdml.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT INMEMORY MEMCOMPRESS FOR DML;

SELECT tablespace_name, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE
COMPIDXADV       DISABLED                                ENABLED      ADVANCED HIGH
COMPMEMDML       DISABLED     FOR DML                    DISABLED
COMPOLTP         ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED

SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute,
def_inmemory_compression, def_inmemory_duplicate
FROM dba_tablespaces
WHERE TABLESPACE_NAME NOT LIKE 'APEX%'
AND TABLESPACE_NAME <> 'TEMP'
ORDER BY tablespace_name;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
---------------- -------- -------- --------------- ----------------- -------------
COMPALL          DISABLED
COMPDEF          DISABLED
COMPDIR          DISABLED
COMPIDXADV       DISABLED
COMPMEMDML       ENABLED  NONE     AUTO            FOR DML           NO DUPLICATE
COMPOLTP         DISABLED
SYSAUX           DISABLED
SYSTEM           DISABLED
UNDOTBS1         DISABLED
USERS            DISABLED
UWDATA          DISABLED
In-Memory Compression for Query DEFAULT INMEMORY MEMCOMPRESS FOR QUERY <LOW | HIGH>
conn sys@pdbdev as sysdba

CREATE TABLESPACE compmemqhigh
DATAFILE 'compmemqhigh.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT INMEMORY MEMCOMPRESS FOR QUERY HIGH;

SELECT tablespace_name, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE
COMPIDXADV       DISABLED                                ENABLED      ADVANCED HIGH
COMPMEMDML       DISABLED     FOR DML                    DISABLED
COMPMEMQHIGH     DISABLED     FOR QUERY HIGH             DISABLED
COMPOLTP         ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED

SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute,
def_inmemory_compression, def_inmemory_duplicate
FROM dba_tablespaces
WHERE TABLESPACE_NAME NOT LIKE 'APEX%'
AND TABLESPACE_NAME <> 'TEMP'
ORDER BY tablespace_name;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
---------------- -------- -------- --------------- ----------------- -------------
COMPALL          DISABLED
COMPDEF          DISABLED
COMPDIR          DISABLED
COMPIDXADV       DISABLED
COMPMEMDML       ENABLED  NONE     AUTO            FOR DML           NO DUPLICATE
COMPMEMQHIGH     ENABLED  NONE     AUTO            FOR QUERY HIGH    NO DUPLICATE
COMPOLTP         DISABLED
SYSAUX           DISABLED
SYSTEM           DISABLED
UNDOTBS1         DISABLED
USERS            DISABLED
UWDATA          DISABLED
In-Memory Compression for Capacity DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY <LOW | HIGH>
conn sys@pdbdev as sysdba

CREATE TABLESPACE compmemcap
DATAFILE 'compmemcap.dbf' SIZE 10M
AUTOEXTEND ON
DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY LOW;

SELECT tablespace_name, def_tab_compression AS DEF_TAB_COMP, compress_for AS COMP_FOR,
       def_inmemory_compression AS DEF_INMEM_COMP, def_index_compression AS DEF_IND_COMP,
       index_compress_for AS IND_COMP
FROM dba_tablespaces
WHERE tablespace_name NOT LIKE 'APEX%'
AND tablespace_name <> 'TEMP'
ORDER BY 1;

TABLESPACE_NAME  DEF_TAB_COMP COMP_FOR  DEF_INMEM_COMP   DEF_IND_COMP IND_COMP
---------------- ------------ --------- ---------------- ------------ --------
COMPALL          ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
COMPDEF          ENABLED      BASIC                      ENABLED      NONE
COMPDIR          ENABLED      BASIC                      ENABLED      NONE
COMPIDXADV       DISABLED                                ENABLED      ADVANCED HIGH
COMPMEMCAP       DISABLED     FOR CAPACITY LOW           DISABLED
COMPMEMDML       DISABLED     FOR DML                    DISABLED
COMPMEMQHIGH     DISABLED     FOR QUERY HIGH             DISABLED
COMPOLTP         ENABLED      ADVANCED                   ENABLED      ADVANCED LOW
SYSAUX           DISABLED                                DISABLED
SYSTEM           DISABLED                                DISABLED
UNDOTBS1         DISABLED                                DISABLED
USERS            DISABLED                                DISABLED
UWDATA           DISABLED                                DISABLED

SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute,
def_inmemory_compression, def_inmemory_duplicate
FROM dba_tablespaces
WHERE TABLESPACE_NAME NOT LIKE 'APEX%'
AND TABLESPACE_NAME <> 'TEMP'
ORDER BY tablespace_name;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
---------------- -------- -------- --------------- ----------------- -------------
COMPALL          DISABLED
COMPDEF          DISABLED
COMPDIR          DISABLED
COMPIDXADV       DISABLED
COMPMEMCAP       ENABLED  NONE     AUTO            FOR CAPACITY LOW  NO DUPLICATE
COMPMEMDML       ENABLED  NONE     AUTO            FOR DML           NO DUPLICATE
COMPMEMQHIGH     ENABLED  NONE     AUTO            FOR QUERY HIGH    NO DUPLICATE
COMPOLTP         DISABLED
SYSAUX           DISABLED
SYSTEM           DISABLED
UNDOTBS1         DISABLED
USERS            DISABLED
UWDATA          DISABLED
 
Table Compression
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.
Basic CREATE TABLE ...
COMPRESS;
conn uwclass/uwclass@pdbdev

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects;

CREATE TABLE comptab
TABLESPACE uwdata COMPRESS AS
SELECT * FROM all_objects;

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

exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');

SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
Advanced Compression Deprecated Direct Load Syntax (but still valid in 12.2) CREATE TABLE ...
COMPRESS FOR DIRECT_LOAD OPERATIONS;
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT /*+ APPEND */ *
FROM all_objects;

CREATE TABLE comptab
TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS
SELECT /*+ APPEND */ *
FROM all_objects;

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

exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');

SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
Advanced Compression COMPRESS CREATE TABLE ...
COMPRESS [BASIC];
conn sh/sh@pdbdev

CREATE TABLE sh_regular AS
SELECT * FROM sales;

CREATE TABLE sh_compressed COMPRESS AS
SELECT * FROM sales;

CREATE TABLE sh_compressed_append COMPRESS AS
SELECT /*+ APPEND */ * FROM sales;

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

SELECT table_name, status, blocks, empty_blocks, instances, secondary, row_movement, compression, compress_for
FROM user_tables
WHERE table_name LIKE 'S%';
Advanced Compression New 12cR1 Syntax CREATE TABLE ...
ROW STORE COMPRESS ADVANCED;
conn sys@pdbdev as sysdba

grant execute on dbms_stats to sh;

conn sh/sh@pdbdev

CREATE TABLE sh_regular AS
SELECT * FROM sales;

CREATE TABLE sh_adv_compressed_append
ROW STORE COMPRESS ADVANCED AS
SELECT * FROM sales;

CREATE TABLE sh_adv_compressed_empty(
prod_id       NUMBER       NOT NULL,
cust_id       NUMBER       NOT NULL,
time_id       DATE         NOT NULL,
channel_id    NUMBER       NOT NULL,
promo_id      NUMBER       NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold   NUMBER(10,2) NOT NULL)
ROW STORE COMPRESS ADVANCED;

INSERT INTO sh_adv_compressed_empty
SELECT /*+ APPEND */ * FROM sales;
COMMIT;

exec dbms_stats.gather_table_stats(USER, 'SH_REGULAR');
exec dbms_stats.gather_table_stats(USER, 'SH_ADV_COMPRESSED_APPEND');
exec dbms_stats.gather_table_stats(USER, 'SH_ADV_COMPRESSED_EMPTY');

col compress_for format a15

SELECT table_name, status, blocks, empty_blocks, instances, secondary, row_movement, compression, compress_for
FROM user_tables
WHERE table_name LIKE 'SH%';
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;
 
Index Compression
Create Prefix Compressed Index

Note: Using ANALYZE INDEX creates locking. An alternative method suggested by Jonathan Lewis involves comparing DISTINCT_KEYS and NUM_ROWS in DBA_INDEXES. If DISTINCT_KEYS is far smaller than NUM_ROWS the index may be a good candidate for compression.

Prefix Compression does not require additional licensing.
CREATE INDEX <index_name>
ON (<first_column>, <second_column>)
PCTFREE <integer>
TABLESPACE <tablespace_name>
COMPRESS <integer *>;


* Must be at least one less than the number of indexed columns
-- for a copy of postal_code visit the Setup page: [Click Here]

conn uwclass/uwclass@pdbdev

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';


-- pay attention to opt_comp_count
Advanced Index Compression (new 12.1) COMPRESS ADVANCED LOW
conn uwclass/uwclass@pdbdev

CREATE INDEX ix_airplanes_custid_deldate
ON uwclass.airplanes(customer_id, delivered_date)
COMPRESS ADVANCED LOW;
Advanced Index Compression (new 12.2) COMPRESS ADVANCED <LOW | HIGH>
conn uwclass/uwclass@pdbdev

CREATE INDEX ix_airplanes_custid_deldate
ON uwclass.airplanes(customer_id, delivered_date)
COMPRESS ADVANCED HIGH;
 
Undo
Undo Segment Compress Undo segment compression is TRUE by default as of Database 11gR1
conn sys@orcl12c as sysdba

set linesize 121
col NAME format a35
col VALUE format a10
col DESCRIPTION format a60

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.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;
 
Net Services Compression
This demo is based on an article published in the January/February 2012 issue of Oracle Magazine by Tom Kyte.

The basic demonstration is replicated here using the class Airplanes table but I strongly recommend everyone read Tom's original.

Download the airplanes table

This qualifies as a "nice to know." I certainly would not write an ORDER BY clause just to maximize compression and decrease bytes sent to client.
conn uwclass/uwclass@pdbdev

desc airplanes

SELECT COUNT(*)
FROM airplanes;

set autotrace traceonly statistics

SQL> SELECT * FROM airplanes;

250000 rows selected.

Statistics
---------------------------------------------------
        30 recursive calls
         1 db block gets
     17740 consistent gets
      1086 physical reads
         0 redo size
  10649995 bytes sent via SQL*Net to client
    183742 bytes received via SQL*Net from client
     16668 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
    250000 rows processed

SQL> SELECT * FROM airplanes ORDER BY program_id, customer_id;

250000 rows selected.

Statistics
----------------------------------------------------------
         1 recursive calls
         1 db block gets
      1090 consistent gets
      1064 physical reads
         0 redo size
   9564424 bytes sent via SQL*Net to client
    183742 bytes received via SQL*Net from client
     16668 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
    250000 rows processed
 
Demos
Create Table in a Compressed Tablespace
conn sys@pdbdev as sysdba

CREATE TABLESPACE compressed
DATAFILE 'compressed.dbf' SIZE 5M
DEFAULT COMPRESS;

SELECT tablespace_name, def_tab_compression
FROM dba_tablespaces;

ALTER USER uwclass
QUOTA unlimited ON compressed;

conn uwclass/uwclass@pdbdev

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects;

CREATE TABLE comptab
TABLESPACE compressed AS
SELECT * from all_objects;

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

exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');

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

conn sys@pdbdev as sysdba

DROP TABLESPACE compressed INCLUDING CONTENTS AND DATAFILES;
Compressed Table with Encrypted Data CREATE TABLE t1 (
testcol VARCHAR2(50))
TABLESPACE uwdata;

CREATE TABLE t2 (
testcol VARCHAR2(50))
TABLESPACE uwdata COMPRESS;

DECLARE
 x t1.testcol%TYPE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT dbms_crypto.randombytes(25)
    INTO x
    FROM dual;

    INSERT INTO t1 VALUES (x);
    INSERT INTO t2 VALUES (x);
  END LOOP;
  COMMIT;
END;
/

-- some sample rows
3605CAA721159CAC4E462B841419CCB7390F1AE3484FF14963
05B7AE0B6BB076EEAF3E8E7DBA1BE9D5C8F97737AA1FDF21A5
40756BCEBF00CCB80ACA5F4F6BF3AFE6BC19D19EA74F10212B
234812A15930421A208BCF19C943762B5FA11D0C0C7E811F5E
4177AFC94C248D6B6765B8CE45FE3E49E2E5456BA6BA48C147

exec dbms_stats.gather_table_stats(USER, 'T1');
exec dbms_stats.gather_table_stats(USER, 'T2');

SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('T1', 'T2');
Compressed Tables: Fixed Length Random Data

Note: The "COMPRESS FOR QUERY" and "COMPRESS FOR ARCHIVE" options are only available on Exadata, ZFS, Pillar and possibly a small number of other Oracle engineered environments.
conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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;
Columnar Storage Exam SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_regular_q;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_ql;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_append_ql;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_qh;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_append_qh;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_al;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_append_al;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_ah;

SELECT DISTINCT dbms_rowid.rowid_block_number(rowid)
FROM sh_compressed_append_ah;
 
Test Cases
Test 1: INSERT Performance conn uwclass/uwclass@pdbdev

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 and Test Results 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
;

Related Topics
Data Pump Compression
DBMS_COMPRESSION
Indexes
LOB Compression
Partitioned Tables
PRVT_COMPRESS
PRVT_COMPRESSION
SecureFiles
Tables
UTL_COMPRESS
UTL_SYS_COMPRESS
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved