Compression

[pranavgovind]

All,

I have questions on COMPRESSION option in oracle9i.

1. I understand, we save space when we use compress option. Is there any other advantage when we use compression option in oracle9i.

2. When we update the row in compressed table, Does oracle require to uncompress before update?

I would appreciate, if you any one can answer for my questions.l

[ebrian]

Table compression will drastically reduce the disk space and buffer cache requirements, and in many cases improve query performance, especially on I/O bound systems. Compression is targeted at decision support and OLAP applications.

Data from a compressed table is read and cached in its compressed format and is decompressed only at data access time. Because data is cached in its compressed form, significantly more data can fit into the same amount of buffer cache. Due to compression, fewer database blocks need to be read from disk, increasing I/O performance and reducing query elapsed time for I/O bound queries.

Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

* Direct path SQL*Loader
* Serial INSERT with an APPEND hint
* Parallel INSERT
* CREATE TABLE ... AS SELECT

Any data that is updated will be uncompressed and stored uncompressed.

[pranavgovind]

Thanks for you reply Ebrain. it is nice explanation. I need one more clarification.

The below command just change the table definition attirbute to COMPRESS. It does not compress the existing data in table. Am i correct?

ALTER TABLE SALES_HISTORY_COMP COMPRESS;

The below command will compress the existing data in a table. Is this correct?
ALTER TABLE SALES_HISTORY_TEMP
MOVE COMPRESS;

[Thomasps]

Why don't you type 4 lines..?

SQL> select extents,bytes/1024/1024 from dba_segments where segment_name='XYZ';

   EXTENTS BYTES/1024/1024
---------- ---------------
       113             113

SQL> alter table xyz compress;

Table altered.

SQL> select extents,bytes/1024/1024 from dba_segments where segment_name='XYZ';

   EXTENTS BYTES/1024/1024
---------- ---------------
       113             113

SQL> alter table xyz move compress;

Table altered.

SQL> select extents,bytes/1024/1024 from dba_segments where segment_name='XYZ';

   EXTENTS BYTES/1024/1024
---------- ---------------
        40              40

All about COMPRESS

http://www.oracle.com/technology/ora...tech_data.html

[pranavgovind]

Thank you.