** Data segment compression

* How does it work?
  Data compression eliminates duplicate values in a database block.

  Compression only occurs when data in loaded with bulk operations.
       For example:
       Direct path SQL*Loader
       Create table ... as select  ...
       Parallel insert
       Direct load insert: insert /*+ append */ ....

  Much like the "nologging" clause, "compress" cluase does not mean compress all data.


* What are the benefits?
  Disk space saving.
  No negative impact on query performance against the compressed data.

* What's the catch?
  Will DML against the data be slower? I think so. No free lunch.
  Will regular DML on the existing data 'decompress' it?

* Notes:
  Compression can be specified at the tablespace level.



* Examples:

SQL> create table tyu (c1 number(3), c2 varchar2(2), c3 char(1))
  2  partition by range (c1)
  3  subpartition by list (c3)
  4  (partition p1 values less than (100) compress
  5   (subpartition p1_d1 values('A'),
  6    subpartition p1_d2 values('B')
  7   ),
  8  partition p2 values less than (200)
  9   (subpartition p2_d1 values('A') compress,
 10    subpartition p2_d2 values('B')
 11   )
 12  );
 (subpartition p2_d1 values('A') compress,
                                 *
ERROR at line 9:
ORA-14160: this physical attribute may not be specified for a table
subpartition


SQL> create table tyu (c1 number(3), c2 varchar2(2), c3 char(1))
  2  partition by range (c1)
  3  subpartition by list (c3)
  4  (partition p1 values less than (100) compress
  5   (subpartition p1_d1 values('A'),
  6    subpartition p1_d2 values('B')
  7   ),
  8  partition p2 values less than (200)
  9   (subpartition p2_d1 values('A'),
 10    subpartition p2_d2 values('B')
 11   )
 12  );

Table created.

SQL> select partition_name,compression from user_tab_partitions where table_name='TYU';

PARTITION_NAME                 COMPRESS
------------------------------ --------
P1                             ENABLED
P2                             NONE

SQL> 


SQL> alter table tyu add constraint tyu_pk primary key (c1);

Table altered.

SQL> create materialized view tyu_mv compress as select * from tyu;

Materialized view created.

-- This will work starting 9.2.0.3
SQL> select compression from user_tables where table_name='TYU';
select compression from user_tables where table_name='TYU'
       *
ERROR at line 1:
ORA-00904: "COMPRESSION": invalid identifier


SQL> create index tyu_idx on tyu(c2) local compress;
create index tyu_idx on tyu(c2) local compress
                        *
ERROR at line 1:
ORA-08113: composite partition index may not be compressed


SQL> drop table tyu;

Table dropped.

SQL> ed
Wrote file afiedt.buf

  1  create table tyu (c1 number(3), c2 varchar2(2), c3 char(1))
  2  partition by range (c1)
  3  (partition p1 values less than (100) compress,
  4  partition p2 values less than (200)
  5*  )
SQL> /

Table created.

SQL> create index tyu_idx on tyu(c2) local compress;

Index created.

SQL> select PARTITION_NAME,COMPRESSION from user_ind_partitions where index_name='TYU_IDX';

PARTITION_NAME                 COMPRESS
------------------------------ --------
P1                             ENABLED
P2                             ENABLED

SQL> 

SQL> alter table tyu rename column c3 to col3;

Table altered.

SQL> insert into tyu values(1,'a','x');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tyu rename column col3 to c3;

Table altered.

SQL> alter table tyu modify (c3 varchar2(3));

Table altered.

SQL> 

-- This bug is fixed in 9.2.0.2
SQL> alter table tyu add (c4 number);
alter table tyu add (c4 number)
                     *
ERROR at line 1:
ORA-22856: cannot add columns to object tables


SQL> 
SQL> drop table tyu;

Table dropped.


-- Change an uncompressed table to a compressed one.
SQL> create table tyu (c1 number);

Table created.

SQL> alter table tyu move compress;

Table altered.

SQL>