| 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
No reason to apologize. Your inputs are always appreciated and without
everybody's input there is no Oracle list.
Regards,
Waleed
-----Original Message-----
From: Tim Gorman [mailto:tim_at_sagelogix.com]
Sent: Tuesday, March 23, 2004 9:49 AM
To: oracle-l_at_freelists.org
Subject: Re: Table compression
Waleed,
My sincere apologies. I ran your test on my laptop and reproduced your
results, using DBA_OBJECTS as the data source (i.e. the "some_table" in
your
script).
I then guessed that perhaps the difference was data. Since the most
important factor for Oracle's table compression algorithm with regards
to
the data is cardinality (i.e. number of distinct data values), I re-ran
the
test using both low-cardinality data and high-cardinality data,
generated
artificially from a PL/SQL block. It still reproduced your results
(i.e.
compressed table deleted as fast or faster).
I went back to my original testing from months ago and realized that I
had
polluted my results by performing an UPDATE of the rows just prior to
performing a DELETE of the rows. I had wanted to test the performance
of
UPDATE as well, but I didn't truncate the table and repopulate before
testing the performance of DELETE. Rewriting the test script to
separate
the operations and re-testing on my laptop then yielded the results you
obtained.
So, thanks for the correction! It's good to learn from mistakes, though I'll try to be more careful not to do it in front of the world next time.
Good work!
-Tim
on 3/23/04 6:55 AM, Khedr, Waleed at Waleed.Khedr_at_FMR.COM wrote:
> Here is a test:
> --
> drop table test_delete_compress;
> --
> create table test_delete_compress noparallel compress as
> select * from some_table where rownum < 100001;
> --
> drop table test_delete_nocompress;
> --
> create table test_delete_nocompress noparallel nocompress as
> select * from test_delete_compress;
> --
> SQL> select segment_name, bytes
> 2 from dba_segments
> 3 where segment_name in
('TEST_DELETE_COMPRESS','TEST_DELETE_NOCOMPRESS');
>
> SEGMENT_NAME BYTES
> ---------------------------------------- ----------
> TEST_DELETE_COMPRESS 7340032
> TEST_DELETE_NOCOMPRESS 17825792
>
> SQL> select count(*) from test_delete_compress;
>
> COUNT(*)
> ----------
> 100000
>
> SQL> select count(*) from test_delete_nocompress;
>
> COUNT(*)
> ----------
> 100000
>
> SQL> alter session set sql_trace = true;
>
> Session altered.
>
> SQL> delete from test_delete_compress;
>
> 100000 rows deleted.
>
> SQL> delete from test_delete_nocompress;
>
> 100000 rows deleted.
>
> Here is the trace file:
>
>
>> >> 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 theupdate
>> 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-8xpenalty on
>> DML. >> >> As with bitmap indices, the benefits of compression come out onlyduring
>> (no difference). >> >> Excerpts from some test timings on a small subset of a fact tablecopied
>> >> 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 compressionratio
>> ma! no caching! >> >> The reason that there is little to no performance penalty on queriesis
>> 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 comparedto
>>> 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 loadwill 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 warehousedatabase
>>>> 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 >> ----------------------------------------------------------------- >> ---------------------------------------------------------------- >> 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 >> -----------------------------------------------------------------
-- 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 Tue Mar 23 2004 - 15:38:59 CST
 |
 |