From oracle-l-bounce@freelists.org Mon Jul 4 07:39:06 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j64Cd6na029359 for ; Mon, 4 Jul 2005 07:39:06 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j64CcvIP029336 for ; Mon, 4 Jul 2005 07:38:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DE56A1C79DF; Mon, 4 Jul 2005 07:38:53 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03766-04; Mon, 4 Jul 2005 07:38:53 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5B9471C7EBE; Mon, 4 Jul 2005 07:38:53 -0500 (EST) Message-ID: <00ff01c58095$16e78040$0301a8c0@porgand> From: =?iso-8859-1?Q?Tanel_P=F5der?= To: "ORACLE-L" References: <52C70FF150F49E479DAF59C68A27149D0164B65D@va016a0e2.corp.suntrust.com> Subject: Re: Compressing partitions Date: Mon, 4 Jul 2005 13:37:06 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00FC_01C5809D.7871EC80" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 22043 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-To: tanel.poder.003@mail.ee X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-1.5 required=5.0 tests=AWL,BAYES_00, FROM_ENDS_IN_NUMS,HTML_50_60,HTML_MESSAGE autolearn=no version=2.63 ------=_NextPart_000_00FC_01C5809D.7871EC80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, If you are talking about historical data, which you won't be modifying = later on, I don't see any real downsides with compression, but with = frequently modified OLTP data, yes.=20 Performancewise, you might want to order your data pysically according = your main index used in range scans on table as well, that way range = scans using this index will me more efficient as table rows are = physically clustered in the order of your index (if performance isn't an = issue, then don't bother). If you want to do physical row ordering, then that's one way to go: create table temp nologging compress as select * from your_table = partition p1 order by col1,col2,col3; create index i1 on temp(col1,col2,col3) nosort nologging compress x; create other indexes... w compress option alter indexes back to logging (if you want to, for maintenance reasons) alter table your_table exchange partition p1 with table temp including = indexes without validation; alter table your_table modify partition p1 logging; Also, a very interesting article regarding efficient partition = exchanging is http://www.dbazine.com/oracle/or-articles/jlewis17 Tanel. ----- Original Message -----=20 From: Kline.Michael=20 To: oracle-l@freelists.org=20 Sent: Monday, July 04, 2005 2:57 AM Subject: Compressing partitions We have several large 2-4 gig partitions on a 5 year basis. After a = month or two, changes should be almost non-existent. They also have partitioned indexes. Just makes things easier when we = roll off the oldest partition, etc. Is there a downside to compression? It's sounds like it's supposed to be more stable now, handling tables = and indexes. While I've not found much, it seems like we could expect the 2-4 gig = to maybe be 200-400 meg??? Even if 25-50% would not be bad given some of = it is 5 years history. We are at 9.2.0.6. Michael Kline Database Administration SunTrust Technology Center 1030 Wilmer Avenue Richmond, Virginia 23227 Outside 804.261.9446 STNet 643.9446 Cell 804.744.1545 michael.kline@suntrust.com =20 LEGAL DISCLAIMER The information transmitted is intended solely for the individual or = entity to which it is addressed and may contain confidential and/or = privileged material. Any review, retransmission, dissemination or other = use of or taking action in reliance upon this information by persons or = entities other than the intended recipient is prohibited. If you have = received this email in error please contact the sender and delete the = material from any computer. =20 Seeing Beyond Money is a service mark of SunTrust Banks, Inc. [ST:XCL] ------=_NextPart_000_00FC_01C5809D.7871EC80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
If you are talking about historical = data, which you=20 won't be modifying later on, I don't see any real downsides with = compression,=20 but with frequently modified OLTP data, yes. 
 
Performancewise, you might want to = order your data=20 pysically according your main index used in range scans on table as = well,=20 that way range scans using this index will me more efficient as table = rows are=20 physically clustered in the order of your index (if performance isn't an = issue,=20 then don't bother).
 
If you want to do physical row = ordering, then=20 that's one way to go:
 
create table temp nologging compress as = select *=20 from your_table partition p1 order by col1,col2,col3;
 
create index i1 on temp(col1,col2,col3) = nosort=20 nologging compress x;
create other indexes... w compress=20 option
 
alter indexes back to logging (if you = want to, for=20 maintenance reasons)
 
alter table your_table exchange = partition p1 with=20 table temp including indexes without validation;
 
alter table your_table modify partition = p1=20 logging;
 
Also, a very interesting article = regarding=20 efficient partition exchanging is http://www.db= azine.com/oracle/or-articles/jlewis17
Tanel.
 
----- Original Message -----
From:=20 Kline.Michael
Sent: Monday, July 04, 2005 = 2:57 AM
Subject: Compressing = partitions

We have several = large 2-4=20 gig partitions on a 5 year basis. After a month or two, changes should = be=20 almost non-existent.

 

They also have = partitioned=20 indexes. Just makes things easier when we roll off the oldest = partition,=20 etc.

 

Is there a = downside to=20 compression?

 

It=92s sounds = like it=92s=20 supposed to be more stable now, handling tables and = indexes.

 

While I=92ve not = found much,=20 it seems like we could expect the 2-4 gig to maybe be 200-400 meg??? = Even if=20 25-50% would not be bad given some of it is 5 years = history.

 

We are at=20 9.2.0.6.

 

Michael=20 Kline
Database=20 Administration
SunTrust =
Technology
=
Center

1030 Wilmer=20 Avenue
Richmond, = Virginia  = 23227
Outside=20 804.261.9446
STNet 643.9446

Cell=20 804.744.1545
michael.kline@suntrust.com

 

 
 
LEGAL DISCLAIMER
The = information=20 transmitted is intended solely for the individual or entity to which = it is=20 addressed and may contain confidential and/or privileged material. Any = review,=20 retransmission, dissemination or other use of or taking action in = reliance=20 upon this information by persons or entities other than the intended = recipient=20 is prohibited. If you have received this email in error please contact = the=20 sender and delete the material from any computer.
 
Seeing = Beyond=20 Money is a service mark of SunTrust Banks, Inc.
[ST:XCL]
=20 =
------=_NextPart_000_00FC_01C5809D.7871EC80-- -- http://www.freelists.org/webpage/oracle-l