Compressed Tables (Part 1)

The examples in this post were done on Oracle 10 with a block size of 8192:

SQL> col value format a10
SQL> l
  1  select value from v$parameter
  2* where name = 'db_block_size'
SQL> /

VALUE
----------
8192

SQL>

You can create a table and check its size like this:

SQL> create table normal_table
  2  as select * from dba_tables;

Table created.

SQL> ed
Wrote file afiedt.buf

  1  select bytes from dba_segments
  2* where segment_name = 'NORMAL_TABLE'

SQL> /

     BYTES
----------
    393216

SQL>

If you create a table in the same way but include the compress clause, the table will end up smaller:

SQL> create table compressed_table compress
  2  as select * from dba_tables;

Table created.

SQL> select bytes from dba_segments
  2  where segment_name = 'COMPRESSED_TABLE';

     BYTES
----------
     65536

SQL>

You can compress an existing table as follows:

SQL> alter table normal_table move compress;

Table altered.

SQL> select bytes from dba_segments
  2  where segment_name = 'NORMAL_TABLE';


     BYTES
----------
     65536

SQL>

And this is how you can uncompress one:

SQL> ed
Wrote file afiedt.buf

  1* alter table compressed_table move nocompress
SQL> /

Table altered.

SQL> ed
Wrote file afiedt.buf


  1  select bytes from dba_segments
  2* where segment_name = 'COMPRESSED_TABLE'
SQL> /

     BYTES
----------
    393216

SQL>