Which oracle version has what Oracle Compression:

8i:
Can's:
IOT compression
Unique and non-Unique index compression

Can'ts:
No table compression
No Materialized Views compression
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


9i:
Can's:
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Adding a column
Dropping a column
No table compression with more than 255 columns
No Bitmap index compression
No LOB compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


10g:
Can's:
Adding a column
Table compression
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
Dropping a column
No table compression with more than 255 columns
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


11g:
Can's:
Dropping a column
Adding a column
Table compression (Row-level compression in a block)
Materialized Views compression
IOT compression
Unique and Non-Unique index compression

Can'ts:
No LOB compression (only with utl_compress package)
No Bitmap index compression

Compression occurs only when:
Direct Path SQL*Loader
CREATE TABLE AS SELECT... (CTAS)
Parallel or serial INSERT statement with an APPEND hit


What is Oracle Advanced Compression in 11g?

OLTP compression (Normal DML's also included)
Compression for Unstructured data like documents, spreadsheets and XML files
RMAN compression
Data Pump DATA compression


How to test the performance impact between compress and no compress tables?

CREATE TABLE comp_test_1 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users;

CREATE TABLE comp_test_2 (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE users
compress;


SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
l_loops NUMBER := 100000;
l_data VARCHAR2(50);
l_start NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE comp_test_2';

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_1 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Normal Insert : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
INSERT INTO comp_test_2 (id, data)
VALUES (i, 'Data for ' || i);
END LOOP;
DBMS_OUTPUT.put_line('Compressed Insert: ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_1
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
UPDATE comp_test_2
set data = 'VDUMPA'
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Update : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_1
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Normal Query : ' || (DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
SELECT data
INTO l_data
FROM comp_test_2
WHERE id = i;
END LOOP;
DBMS_OUTPUT.put_line('Uncompressed Query : ' || (DBMS_UTILITY.get_time - l_start));
END;
/