This discussion is archived
6 Replies Latest reply: Jun 26, 2012 2:39 AM by 930168 RSS

advaced compression in oracle 11g

882501 Newbie
Currently Being Moderated
Hi,

We are migrating databases from oracel 10g to 11g and we are using advance compression, i have few question please help me to understand

1. if i enable compression on tables is index also get compressed if not how i can enable compression on indexes

2.For table compression i will take the DDL of tables from oracle 10g databases and i create the tables in oracle 11g with COMPRESS FOR ALL OPERATIONS is this the right approach


Appreciated the inputs

thanks
  • 1. Re: advaced compression in oracle 11g
    864403 Explorer
    Currently Being Moderated
    1. No , Indexes will have to be rebuild when ever tables are compressed. Specify compress for indexes when you rebuild.

    2. No, COMPRESS FOR ALL OPERATIONS (Same as COMPRESS FOR OLTP) requires additional licensing. You have to use COMPRESS which defaults to 10g.

    http://docs.oracle.com/cd/E11882_01/license.112/e10594.pdf

    Thanks
  • 2. Re: advaced compression in oracle 11g
    882501 Newbie
    Currently Being Moderated
    Thanks for the input my scenerio is we are migrating to oracle 11g from 10g and we have licence for the 11g advanced compression

    so to implement this feature : from 10g databases i will take only ddl statements belongs to all tables and create the tables with COMPRESS FOR ALL OPERATIONS in 11g database and import the table data in to oracle 11g with table_exists_action=truncate so here both tables and indexes are compressed and i eanbled the advanced compression in 11g is iam correct ?

    Thanks
  • 3. Re: advaced compression in oracle 11g
    882501 Newbie
    Currently Being Moderated
    appreciated any inputs

    Thanks
  • 4. Re: advaced compression in oracle 11g
    864403 Explorer
    Currently Being Moderated
    I would recommend upgrade to 11g using conventional approach and issue ALTER TABLE MOVE COMPRESS FOR ALL OPERATIONS. This is much cleaner approach.

    Thanks
  • 5. Re: advaced compression in oracle 11g
    882501 Newbie
    Currently Being Moderated
    Hi,

    I checked for one of the table ALTER TABLE MOVE COMPRESS FOR ALL OPERATIONS after upgrading to 11g from 10g and rebuild the index
    SQL> select index_name,COMPRESSION,STATUS from dba_indexes where table_name='POSITION_CUBE';
    
    INDEX_NAME                     COMPRESS STATUS
    ------------------------------ -------- --------
    TEST                           DISABLED VALID
    still compress column in dba_indexes show disabled

    so i need to compress index also , how i can achive this

    Thanks
  • 6. Re: advaced compression in oracle 11g
    930168 Newbie
    Currently Being Moderated
    try

    alter index <index name> rebuild compress

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points