How to compress tables in Oracle 9i

[ksridhar]

Hi,

I am working in Data warehousing project as per my client requirement i have to compress the tables using the feature in Oracle 9i Release 2. As Data warehouse contains Millions of records i need to cmopress and also test the Query performance of Compressed tables in oracle 9i against uncompressed tables in Oracle 8i, I read many articles regarding Compression of tables in oracle 9i but no where did they mentioned how to compress the tables!!! can anyone pls help me

Thanks
Sridhar

[denevge]

I think you will have to create a new table, with compression enabled and afterwards insert the data into this new table.

HTH
Gert

[slimdave]

I think that some reading of documentation is called for.

You can "ALTER TABLE ... COMPRESS" to enable compression for new data, or "ALTER TABLE ... MOVE COMPRESS" to compress data already in the table.

PANDO, I think you were a little premature in closing ksridhar's thread, eh?

[mike9]

When you are inserting the data you have to use the APPEND option with the INSERT statement or DIRECT LOAD if you are using SQLLOADER otherwise your data will not be compressed.

HTH
Mike

[pando]

premature? I close dupe threads nothing else nothing more

[ksridhar]

Hi Mr Pando!!

The reason for Discussion forum is to get the knowledge or to get a solution from experienced guys in taht area .. What made u think that this is a Dupe thread i strongly oppose this !!! As i could not get much info regarding Oracle 9i compression feature so thought i could post in it this forum to get a solution... I feel bad regarding the closing of this thread

Thanks
Sridhar

[pando]

you post in a place not two like you do in two forums, that's dupe and will always be closed, people not only visit in one forum so what's the point posting in two?

what if everyone acts like you and post twice all questions? post twice doesnt mean you get your answers

I dont understand why you say you cannot get much info on table compression because it's all explained in the documentation

[slimdave]

sorry pando, didn't spot that both threads were Sridhar's.

[ksridhar]

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 < >
),