Compress data in Table/Partition

As application usage increases, data in the database grows pretty quickly. In some applications there are driving tables whose data grows very quickly as users adds more data to the system. If you are in a environment where database service providers who charges on the database size usage, will have to considering increasing expenses as database size increases.

Oracle has compression technique for long time, but it is not popular feature as it always needed a trade off between performance and space. Oracle 10g and up has introduced advanced compression techniques which is very attractive  and unique in a way it compresses data. Here is this works

• Oracle eliminates duplicate values in the database block. Instead it uses pointers for repeated information (called symbols). Header will hold all the unique values.
• Block is self-contained that is block contains all information needed to re-construct uncompressed data
• Since it eliminates duplicates, it can compress to maximum potential especially in scenarios where applicaiton has audit table – has lot of repeated information (historical change record).
• This compression works for all data types except for variants of LOBs
• This compression do not trigger for every write operation; instead when block reaches a threshold (manager internally) compression occurs on that block. Only few transactions triggers this event.
• Side effect:It is possible to cause fragmantation when there are many modification to table/partition. Ex – if a row is deleted, it is less likely new data will fit into freed-up space. For such tables re-compress the data.

So how do I compress table?
As simple command like the below can compress the table for OLTP operations

SQL> alter table test_tab compress for OLTP;

To compress the table partition use below command

SQL > alter table test_tab modify[move] partition test_tab_2011 compress for oltp;

However this do not compress already existing data. Let’s look at following statements for partitions.

Below statement gets me blocks and size occupied for the given partition

SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
2 from user_segments
3 where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
4 /
BLOCKS_KB BYTES_KB
---------- ----------
12 96
1 row selected.
Elapsed: 00:00:00.08

Now lets compress the table partition using modify option.

SQL> alter table TEST_TAB modify partition TEST_TAB_2011 compress for oltp
2 /
Table altered.
Elapsed: 00:00:00.03
SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
2 from user_segments
3 where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
4 /
BLOCKS_KB BYTES_KB
---------- ----------
12 96
1 row selected.

As you can see from above statement, after enabling compression on partition, same block and size remained as before. It means no data compressed, we just enabled compression.

Now let’s use Move option for alter table.

SQL> alter table TEST_TAB move partition TEST_TAB_2011 compress for oltp;
Table altered.
Elapsed: 00:00:12.41
SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
  2  from user_segments
  3  where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
  4  /
BLOCKS_KB   BYTES_KB
---------- ----------
         6         48
1 row selected.
Elapsed: 00:00:00.04

Now total size for partition is down to 48KB (earlier it was 96 KB). It means existing data is compressed.

Because of the above reasons, it is also possible that table/partition may contain compressed and non-compressed data.

How to compress existing data in a non partitioned table?

Here are options…

• Export table data, drop data, enable compression using alter table statement (first statement) then re-load data [OR]
• Re-name existing table, create new compressed table, load new compressed table then drop the renamed table.
• Well you can use the above options…but better yet use move option with alter table command. Here is the example.

SQL> create table kp_test2 as select * from dba_objects;
Table created.
Elapsed: 00:00:02.07
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 1 8
Elapsed: 00:00:00.40
SQL> alter table kp_test2 compress for oltp;
Table altered.
Elapsed: 00:00:00.03
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 1 8
Elapsed: 00:00:00.13

With the above command, segment size remained same as before compression enabled.

SQL> alter table kp_test2 move compress for oltp;
Table altered.
Elapsed: 00:00:00.75
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 .375 3
Elapsed: 00:00:00.08

Wow,  size reduced to 3KB by using move option.

Read Full Post »