Re: Any idea how compression works in Oracle 9.2

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 - 03:36:18 CST