6 Replies Latest reply: Jun 26, 2012 4:39 AM by 930168 RSS

    advaced compression in oracle 11g

    882501
      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
          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
            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
              appreciated any inputs

              Thanks
              • 4. Re: advaced compression in oracle 11g
                864403
                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
                  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
                    try

                    alter index <index name> rebuild compress