| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table compression
Don't know why you think deletion in compression tables will be that slow.
I expect deletion speed not be affected by the compression. Also conventional inserts should no be affected since it will not be compressed.
On the other hand I know that updates are really slow and usually the update ends up with tons of chained rows that make FTS a nightmare. When I need to update, I take these steps: uncompress, update, compress.
Regards,
Waleed
-----Original Message-----
From: Tim Gorman [mailto:tim_at_sagelogix.com]
Sent: Monday, March 22, 2004 9:03 AM
To: oracle-l_at_freelists.org
Subject: Re: Table compression
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
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
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: >
-- 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 - 09:58:48 CST
 |
 |