Re: Any idea how compression works in Oracle 9.2

On 28 Jan 2003 01:36:18 -0800, kada_sridhar_at_hotmail.com (Sridhar) wrote:

Get the below table. Mostly a data warehouse fix for the moment.

Table Compression in Oracle9i
Release2
An Oracle White Paper
May 2002
>Hi ,
>
> The Table compression works for Partitioned and non-partitioned
>tables the syntax for Non-Partitioned tables is
>the statement "ALTER TABLE <table name> MOVE COMPRESS"
>is going to compress the existing rows and also the New rows to be
>added
>
>"ALTER TABLE EMP
> MERGE PARTITIONS P_2000,P_3000 INTO PARTITION
>P_2000_3000_compress
> COMPRESS;" is going to merge and compress the partitions
>saving lot of disk space.
>
>for a Partitioned Tables u can only merge and compress or if u want to
>compress a partitioned table ur going to loose the partitions
>"create table <table_name_compress>
>tablespace <tablespace name>
>COMPRESS
>as
>select * from <tablename>"
>
>I have worked on Table Compression feature the results are amazing!!
>The Table size was reduced from 800MB to 120MB and 230MB to 32 MB
>The query performance is also good.. these results were done on
>Partitioned Tables!!! all partitions were removed... This is
>recommended on Tables where no DMLs are performed. If i come to know
>any results i'll post them
>
>compression for partitioned tables
>USE THIS way and also remove the clause COMPRESS in this definition
>load the data in both the tables see the table size i did not find and
>difference in the table size!!!
>
>CREATE TABLE EMP
>(EMPNO NUMBER(4) NOT NULL,
>ENAME CHAR(30),
>AGE NUMBER(3)
>)
>COMPRESS
>PARTITION BY RANGE(EMPNO)
>(
>PARTITION P_1000 VALUES LESS THAN (1000)
>TABLESPACE <tablespace name>
>STORAGE PARAMETERS < >
>),
>(
>PARTITION P_2000 VALUES LESS THAN (2000)
>TABLESPACE <tablespace name>
>STORAGE PARAMETERS < >
>),
>(
>PARTITION P_3000 VALUES LESS THAN (3000)
>TABLESPACE <tablespace name>
>STORAGE PARAMETERS < >
>),
>
>May be this can help u guys on Table Compression.
>
>"Cesar Otero Souto" <cotero_at_most.com.ar> wrote in message news:<0KZddtAuCHA.302_at_newsgroup.korea.com>...
>> On Thu, 09 Jan 2003 09:29:13 +0000, Niall Litchfield wrote:
>>
>> > Is this any help?
>> >
>> > http://otn.oracle.com/products/bi/pdf/o9ir2_compression_twp.pdf
>> >
>> >
>> > --
>> > Niall Litchfield
>> > Oracle DBA
>> > Audit Commission UK
>> > *****************************************
>> > Please include version and platform
>> > and SQL where applicable
>> > It makes life easier and increases the
>> > likelihood of a good answer
>> >
>> > ******************************************
>> > "Rama Krishna B.K." <rkenchab_at_netscape.net> wrote in message
>> > news:v1qfa0g04fo73c_at_corp.supernews.com...
>> >> Hi ,
>> >>
>> >> I have started working on Oracle 9.2 recently.
>> >>
>> >> Please let me know if anybody knows how table compression works in
>> >> Oracle 9.2
>> >>
>> >> Thanks in advance,
>> >> Ramki
>> >>
>> Very good Article Niall
>> Ronia
Received on Tue Jan 28 2003 - 04:58:03 CST