Re: Table compression

From: Tim Gorman <tim_at_sagelogix.com>
Date: Mon, 22 Mar 2004 07:03:03 -0700
Message-ID: <BC844027.11D94%tim@sagelogix.com>

LeRoy,

Expect performance penalties of 2x on times for "bulk" inserts, 3x-4x penalty on "conventional" inserts and updates, and up to 6x-8x penalty on deletes. This is an option that shares a lot of design characteristics with bitmap indexes, in that you love them for the queries but hate them during DML. As with bitmap indices, the benefits of compression come out only during certain query operations. Queries using full table scans are much, MUCH faster (due simply to fewer I/Os) and queries using indexed scans are a wash (no difference).

Excerpts from some test timings on a small subset of a fact table copied into (unindexed and nonparallel) compressed and non-compressed copies:

SQL> select sum(bytes)/1048576 alloc_mb,

  2          sum(blocks) alloc_blocks,
  3          count(distinct partition_name) nbr_partitions,
  4          count(*) nbr_exts

5 from user_extents
6 where segment_name = 'COMPRESS_TEST';

ALLOC_MB ALLOC_BLOCKS NBR_PARTITIONS NBR_EXTS ---------- ------------ -------------- ----------

194.875 24944 51 538

SQL> select count(*) from compress_test;

COUNT(*)

6443786

1 row selected.

Elapsed: 00:00:07.40

SQL> select sum(bytes)/1048576 alloc_mb,

  2          sum(blocks) alloc_blocks,
  3          count(distinct partition_name) nbr_partitions,
  4          count(*) nbr_exts

5 from user_extents
6 where segment_name = 'NONCOMPRESS_TEST';

ALLOC_MB ALLOC_BLOCKS NBR_PARTITIONS NBR_EXTS ---------- ------------ -------------- ---------- 792.0625 101384 51 1110

SQL> select count(*) from noncompress_test;

COUNT(*)

6443786

1 row selected.

Elapsed: 00:00:56.48

In this case, a 4:1 compression ratio on space but a 7:1 compression ratio on FULL table scan performance. A little less I/O goes a long way, and look ma! no caching!

The reason that there is little to no performance penalty on queries is because Oracle's method of compression is not similar to the algorithm's used by "zip" or "compress" or "zcat", etc. Rather, Oracle adds a layer of indirection to a list of distinct values within the block, so that rows become a set of pointers to this list (called a "symbol table") within the block. Little-to-no additional cycles to "uncompress". This style of "compression" pays off when there is low cardinality and lots of repeated data values, such as the case with fact tables in a dimensional data model.

So this is an incredibly useful option for data warehouses but you have to use bulk loads (a.k.a. inserts via direct-path, append, etc) which, in most circumstances, means using partitioning and various forms of the EXCHANGE PARTITION load technique...

Hope this helps...

-Tim

on 3/19/04 1:58 PM, LeRoy Kemnitz at lkemnitz_at_uwsa.edu wrote:

> I read that the select runs just as fast if not faster when compared to
> uncompressed tables.
> 
> 
> 
> Goulet, Dick wrote:
>

>> Yeah, but what's the penalty during reads???
>>
>> Dick Goulet
>> Senior Oracle DBA
>> Oracle Certified 8i DBA
>>
>> -----Original Message-----
>> From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
>> Sent: Friday, March 19, 2004 3:48 PM
>> To: 'oracle-l_at_freelists.org'
>> Subject: RE: Table compression
>>
>>
>> I use it, works great, 60% savings.
>>
>> Once the table/partition gets flagged "compress", any direct load will be
>> compressed.
>>
>> You will get ora-600 if trying to do parallel direct load.
>>
>> Also can't add a column to a compressed table.
>>
>> Waleed
>>
>> -----Original Message-----
>> From: LeRoy Kemnitz [mailto:lkemnitz_at_uwsa.edu]
>> Sent: Friday, March 19, 2004 3:41 PM
>> To: Oracle List
>> Subject: Table compression
>>
>>
>>
>> I am looking into doing some table compression on my warehouse database
>> to free up some space on the os. I am running 9.2.0.4 on Unix 5.1. The
>> compression is about 2.5:1 on my tables. The documentation says the
>> bulk insert time will be doubled but the single inserts, updates, and
>> deletes are going to be a wash. Does anyone use compression? Are there
>> any problems you notice in the use of it? I have also read that the
>> table will need to be re-compressed after the bulk inserts. Any
>> alternative ideas about getting this done?
>>
>> Thanks in advance,
>>
>> LeRoy
>>
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> ----------------------------------------------------------------
>> To unsubscribe send email to: oracle-l-request_at_freelists.org
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> ----------------------------------------------------------------
>> To unsubscribe send email to: oracle-l-request_at_freelists.org
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> ----------------------------------------------------------------
>> To unsubscribe send email to: oracle-l-request_at_freelists.org
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>>
>>
>>
>>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 22 2004 - 08:00:35 CST