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