3 Replies Latest reply: Mar 6, 2012 7:06 PM by ursusca RSS

    Index

    891445
      Hi,

      If i happen to move a table to an compressed table, does the assoicated Indexes get invalidated ?
      When i check, the status is still valid. Is it recommended to rebuild the index after table is compressed.

      Also, does advanced compression allow us to do Index compression as well ?
      Is there any way to do the Table and its associated indexes to be compressed in one go ?
      oracle version: 11.1.0.7

      Thank u.
        • 1. Re: Index
          ursusca
          Moving a table will invalidate an index. To compress an index, simply specify the COMPRESS option:

          CREATE INDEX ... COMPRESS 3;

          The number after the COMPRESS means how many columns to compress. The default is all columns in a non-unique index and all columns except the last column in a unique index.

          Edited by: ursusca on Mar 6, 2012 2:45 PM

          Edited by: ursusca on Mar 6, 2012 3:40 PM
          • 2. Re: Index
            damorgan
            Your question is far from clear but perhaps this will help.
            orabase> create table t as
              2  select * from user_tables;
            
            Table created.
            
            orabase> create index ix_t on t(table_name);
            
            Index created.
            
            orabase> select count(*) from user_indexes where status = 'INVALID';
            
              COUNT(*)
            ----------
                     0
            
            orabase> alter table t compress for all operations;
            
            Table altered.
            
            orabase> select count(*) from user_indexes where status = 'INVALID';
            
              COUNT(*)
            ----------
                     0
            
            orabase> alter table t move tablespace SYSTEM;
            
            Table altered.
            
            orabase> select count(*) from user_indexes where status = 'INVALID';
            
              COUNT(*)
            ----------
                     0
            • 3. Re: Index
              ursusca
              damorgan, you forgot to move a table. After compressing a table, you need to rebuild indexes because the rowid's have changed.
              SQL> create table t as
              2  select * from user_tables;
              
              Table created.
              
              SQL> create index ix_t on t(table_name);
              
              Index created.
              
              SQL> select count(*) from user_indexes where status <> 'VALID';
              
              COUNT(*)
              ----------
              0
              
              SQL> alter table t compress for all operations;
              
              Table altered.
              
              SQL> alter table t move;
              
              Table altered.
              
              SQL> select count(*) from user_indexes where status <> 'VALID';
              
              COUNT(*)
              ----------
              1
              The ALTER TABLE ... COMPRESS .... command doesn't compress the existing data in the table immediately.

              Edited by: ursusca on Mar 6, 2012 8:04 PM