ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
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;
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;
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;
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;
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;
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;
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
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');
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;
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;
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;
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 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;
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 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;
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;
