Re: Table compression
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Mar 2004 07:03:03 -0700
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@xxxxxxxx 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@xxxxxxx]
>> Sent: Friday, March 19, 2004 3:48 PM
>> To: 'oracle-l@xxxxxxxxxxxxx'
>> 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@xxxxxxxx]
>> 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@xxxxxxxxxxxxx
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at //www.freelists.org/archives/oracle-l/
>> FAQ is at //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@xxxxxxxxxxxxx
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at //www.freelists.org/archives/oracle-l/
>> FAQ is at //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@xxxxxxxxxxxxx
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at //www.freelists.org/archives/oracle-l/
>> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
