CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Advanced Compression Tables
Version 11.1
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
 
Compressed Table

COMPRESSED
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 Tablespace
CREATE TABLESPACE ...
DEFAULT COMPRESS;
conn / 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

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

CREATE TABLE comptab
TABLESPACE compressed AS
SELECT * from all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

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 / as sysdba

DROP TABLESPACE compressed INCLUDING CONTENTS AND DATAFILES;

Compressed Table

New in 11g
CREATE TABLE ...
COMPRESS;
conn uwclass/uwclass

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

CREATE TABLE comptab
TABLESPACE uwdata COMPRESS AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

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

Another example
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');

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

Compressed Direct Load

New in 11g
CREATE TABLE ...
COMPRESS FOR DIRECT_LOAD OPERATIONS;
CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT /*+ APPEND */ *
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

CREATE TABLE comptab
TABLESPACE uwdata COMPRESS FOR DIRECT_LOAD OPERATIONS AS
SELECT /*+ APPEND */ *
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

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');
 
Compressed 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
Uncompressing an index ALTER INDEX <index_name> NOCOMPRESS;
ALTER INDEX cix_pcodes NOCOMPRESS;
 
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
    WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
  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
    WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
  END LOOP;
  COMMIT;
END comptest1;
/

Test 2: UPDATE Performance
SELECT MIN(object_id), MAX(object_id)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

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;
 
Related Topics
Indexes
LOB Compression
Partitioned Tables
SecureFiles
Tables
UTL_COMPRESS
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [266 users online]    © 2010 psoug.org