Compressed Tables (Part 2)

This is an example to show the effect of block sizes on Oracle compression. The SQL was run on an Oracle 9 database with a 4k block size:

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

VALUE
----------
4096

SQL>

Before starting, I allocated space in the buffer cache for all the non-standard block sizes I planned to use:

SQL> col name format a20
SQL> select name, value from v$parameter
  2  where name like 'db_%k_cache_size'
  3  /

NAME                      VALUE
-------------------- ----------
db_2k_cache_size        8388608
db_4k_cache_size              0
db_8k_cache_size        8388608
db_16k_cache_size       8388608
db_32k_cache_size       8388608

SQL>

Then I created tablespaces with various block sizes:

SQL> create tablespace two_k
  2  datafile size 20m blocksize 2k
  3  /

Tablespace created.

SQL> create tablespace four_k
  2  datafile size 20m
  3  /

Tablespace created.

SQL> create tablespace eight_k
  2  datafile size 20m blocksize 8k
  3  /

Tablespace created.

SQL> create tablespace sixteen_k
  2  datafile size 20m blocksize 16k
  3  /

Tablespace created.

SQL> create tablespace thirty_two_k
  2  datafile size 20m blocksize 32k
  3  /

Tablespace created.

SQL>

Then I checked that the tablespaces had been created with the block sizes specified. I did this because the example did not work as expected at first:

SQL> col tablespace_name format a15
SQL> select tablespace_name, block_size
  2  from dba_tablespaces
  3  where tablespace_name like '%_K'
  4  order by block_size
  5  /

TABLESPACE_NAME BLOCK_SIZE
--------------- ----------
TWO_K                 2048
FOUR_K                4096
EIGHT_K               8192
SIXTEEN_K            16384
THIRTY_TWO_K         32768

SQL>

Then I created a table with some repeated data:

SQL> create table repeated_data
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2  for a in 1..5 loop
  3  insert into repeated_data
  4  select * from repeated_data;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

I copied this table into each tablespace. At first I tried it without the order by statement. But when I compressed the tables, the space saved was the same, irrespective of block size. That’s because the compression is done at block level. If Oracle sees data repeated within a block, it sets up a symbol for it in a symbol table in the same block. Then it replaces every occurrence of that repeated data in the block by the appropriate symbol. I guess the effect of the order by statement is to place more repeating data within the same block. This gives the compression algorithm something to work on. The larger block sizes then result in better compression because they need to store fewer copies of the symbol tables:

SQL> create table repeated_data_2k
  2  tablespace two_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_4k
  2  tablespace four_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_8k
  2  tablespace eight_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_16k
  2  tablespace sixteen_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_32k
  2  tablespace thirty_two_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

Next, I checked the starting sizes of the tables and compressed them:

SQL> col segment_name format a20
SQL> select segment_name, bytes
  2  from dba_segments
  3  where segment_name like 'REPEATED_DATA_%K'
  4  /

SEGMENT_NAME              BYTES
-------------------- ----------
REPEATED_DATA_8K        6291456
REPEATED_DATA_16K       6291456
REPEATED_DATA_32K       6291456
REPEATED_DATA_2K        7340032
REPEATED_DATA_4K        7340032

SQL> alter table repeated_data_2k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_4k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_8k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_16k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_32k
  2  move compress
  3  /

Table altered.

SQL>

Finally, I checked the table sizes after compression. The tables in the tablespaces with larger block sizes were compressed much more:

SQL> select segment_name, bytes
  2  from dba_segments
  3  where segment_name like 'REPEATED_DATA_%K'
  4  /

SEGMENT_NAME              BYTES
-------------------- ----------
REPEATED_DATA_8K         720896
REPEATED_DATA_16K        524288
REPEATED_DATA_32K        458752
REPEATED_DATA_2K        2097152
REPEATED_DATA_4K         917504

SQL>