Oracle 11gR2 Table level compression.

Earlier in oracle 10g we had compression at the table level but it
was only for bulk load operations.

But with Oracle 11gR2 compression is also available at the OLTP
level.

In other words we have now DML level compression on tables.

Main advantages of this new compression feature is as following.

1) 2-4X reduction in storage.
2) Improved memory efficiency as data remains compressed in memory.
3) Minimal or no performance impact on DML operations.
4) Faster queries in many cases, due to improved I/O and memory
   efficiency.
5) Better network bandwidth utilization.
6) Transparent to applications - no application changes required.

A slight disadvantage is that compression is CPU hungry.

with 11gR2 we have four types of compression at the table level.

1) Basic Compression
2) OLTP Compression
3) Warehouse compression (Hybrid Columnar Compression)
4) Online archival compression (Hybrid Columnar Compression)


From the above four we will only be focusing
on basic and OLTP compression as the other two
are only available on ORACLE EXADATA V2 hardware.

BASIC COMPRESSION

Create a table with basic compression.

SQL> create tables test(
     id int,
     name varchar2(24)) COMPRESS BASIC;


in this type of compression only Direct-path inserts are compressed.

insert /*+ APPEND */ into test select id, ename from employees;

Rows which are inserted without using direct path insert and
updated rows are uncompressed.

If the table is already there and you want to compress it then,

SQL> alter table test compress;

The above command will only compress subsequent insertions.
If you want full compression of the table then execute:

SQL> alter table test move compress; 

NOTE: This command will make the table unavailable for 
      some time until the compression of the whole table
      is complete.

To turn off compression.

SQL> alter table test nocompress;

Above command will result in noncompression of subsequent insertions,
existing data will not be uncompressed.

To completely remove compression.

SQL> alter table test move nocompress;

OLTP COMPRESSION


OLTP compression feature is available at the DML level.
That means all DML changes are compressed.

Create a table with OLTP compression.

SQL> create tables test(
     id int,
     name varchar2(24)) COMPRESS FOR OLTP;


If there is already a table which you would like to compress
do the following.

SQL> alter table employees move compress for OLTP;

NOTE: This command will make the table unavailable for 
      some time until the compression of the whole table
      is complete.



If you want to turn the table compression off just execute

the following command.

SQL> alter table employees move nocompress;

or

If You just want compression feature off for subsequent DML
statements.

SQL> alter table employees nocompress;

EXAMPLE:

SQL> alter user hr identified by hr account unlock;

SQL> conn hr/hr
Connected.

SQL> create table test as select * from all_objects;

Table created.

SQL> select bytes/1024/1024 from user_segments where
     segment_name = 'TEST';

BYTES/1024/1024
---------------
              7


SQL> insert into test select * from test;

55666 rows created.

SQL> /

111332 rows created.

SQL> /

222664 rows created.

SQL> /

445328 rows created.

SQL> /

890656 rows created.

SQL> /

1781312 rows created.

SQL> /

3562624 rows created.

SQL> /

7125248 rows created.

SQL> 

Lets check the size of the table.

SQL> select bytes/1024/1024 from user_segments where
     segment_name = 'TEST';

BYTES/1024/1024
---------------
           1666


Almost 1.7GB

Now lets do compression.


SQL> alter table test move compress for OLTP;

Table altered.

SQL> select bytes/1024/1024 from user_segments where
         segment_name = 'TEST';

BYTES/1024/1024
---------------
            504


Almost 30% compression.

Higher compression can also be there but that all depends
on the type of data that you have.