OLTP Compression

OLTP compression is part of the Advanced Compression database option, and builds on the BASIC compression available for free.  Unlike BASIC, it doesn’t require direct path load operations and will compress after regular INSERT and UPDATE operations.

In this post I want to demonstrate when the compression kicks in, and how you can prove that a given row is compressed.

The compression algorithm basically replaces any duplicated values within that block into a symbol table stored at the beginning of the block.

The other interesting thing about OLTP compression is that it won’t compress data immediately.  Data within a block will only be compressed when the block becomes full (less PCT_FREE).  By default a table compressed with OLTP compression will be assigned a PCT_FREE of 10.  Therefore, the block will only be compressed when it becomes 90% full.  The block will be compressed again next time it fills, and so it goes on until it becomes 90% full of compressed data.

The other benefit of this behaviour is that it constantly de-fragments blocks that become fragmented due to delete operations.

Use DBMS_COMPRESSION.GET_COMPRESSION_TYPE(‘SCHEMA’,’TABLE’,’ROWID’) to show what type of compression has been applied to a specific row.  It will return an integer to denote the compression type –

1 – No compression
2 – OLTP
4 – HCC Query High
8 – HCC Query Low
16 – HCC Archive High
32 – HCC Archive Low

We can see this behaviour in action with the following test case. I want to demonstrate firstly how you see which rows are under which compression, and also why data won’t show as being compressed until it fills a block. We’re using my @show_compression_count.sql script – careful about using this against large production tables as it takes a while running against anything with more than a few thousand rows.

First create the table –


SQL> create table t1 (col1 varchar2(50)) compress for oltp;
Table created.

SQL> select pct_free from user_tables where table_name = 'T1';
PCT_FREE
----------
10

Now we insert one row, and check what compression has been applied to it –


SQL> insert into t1 values ('Hello world');
SQL> @show_compression_count

Enter value for schema: TEST_SCHEMA
Enter value for table: T1

COMPRESSION_TYPE COUNT(*)
---------------- ----------
NOCOMPRESS       1

So we can see that the data hasn’t been compressed.  That is because it hasn’t filled the block yet.  Lets try putting in another 20 rows –


begin
for counter in 1..20
loop
insert into t1 values ('Hello world');
end loop;
end;
/
commit;

SQL> @show_compression_count
Enter value for schema: TEST_SCHEMA
Enter value for table: T1

COMPRESSION_TYPE COUNT(*)
---------------- ----------
NOCOMPRESS       21

Still not filled the block!  Lets try 2,000 rows!


begin
for counter in 1..2000
loop
insert into t1 values ('Hello world');
end loop;
end;
/

SQL> @show_compression_count
Enter value for schema: TEST_SCHEMA
Enter value for table: T1

COMPRESSION_TYPE COUNT(*)
---------------- ----------
NOCOMPRESS       737
OLTP             1284

OK, finally filled the block which has triggered the compression.  It looks like we’ve overflowed one block and are left with another block which hasn’t yet compressed.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: