Compressing Oracle index via DBMS_REDEFINITION.

step-1-create.sql

set pagesize 100 linesize 120 long 10000 echo on 
select * from PRODUCT_COMPONENT_VERSION
;
-- clear schema
drop table foo
;
drop table foo_tmp
;
create table foo (
  id number(10) constraint foo_pk primary key,
  year_month varchar2(6 char) not null,
  tx_id number(10) not null,
  data varchar2(1000)
)
partition by range ( year_month )
( 
  partition p201101 values less than ( '201101' ),
  partition p201102 values less than ( '201102' ),
  partition p201103 values less than ( '201103' ),
  partition p201104 values less than ( '201104' ),
  partition pMAX values less than ( MAXVALUE ) 
)
;
insert into foo
select level, '2011' || substr(to_char(mod(level, 10) + 1, '00'), 2)
, mod(level, 7)
, rpad('*', 100 + mod(level, 900), '*')
from dual
connect by level <= 1000
;
select max(id) as max_id
from foo
;
create index foo_ym_tx on foo ( year_month, tx_id )
local
;
select dbms_metadata.get_ddl('INDEX', 'FOO_YM_TX') from dual
;
-- redef preparation:
create table foo_tmp (
  id number(10) constraint foo_tmp_pk primary key,
  year_month varchar2(6 char) not null,
  tx_id number(10) not null,
  data varchar2(1000)
)
partition by range ( year_month )
( 
  partition p201101 values less than ( '201101' ),
  partition p201102 values less than ( '201102' ),
  partition p201103 values less than ( '201103' ),
  partition p201104 values less than ( '201104' ),
  partition pMAX values less than ( MAXVALUE ) 
)
;
create index foo_tmp_ym_tx on foo_tmp ( year_month, tx_id )
compress 1
local
;
quit

step-2-redef.sql

set pagesize 100 linesize 120 long 10000 echo on
call DBMS_REDEFINITION.CAN_REDEF_TABLE(upper('&&Schema'), 'FOO')
;
select dbms_metadata.get_ddl('INDEX', 'FOO_YM_TX', upper('&&Schema')) from dual
;
timing start redef
begin
  -- just in case there are leftovers of previous tests:
  DBMS_REDEFINITION.ABORT_REDEF_TABLE(upper('&&Schema'), 'FOO', 'FOO_TMP');
  
  -- now let's go!
  DBMS_REDEFINITION.START_REDEF_TABLE(upper('&&Schema'), 'FOO', 'FOO_TMP');
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
    upper('&&Schema'), 'FOO', 'FOO_TMP', 
    DBMS_REDEFINITION.CONS_INDEX, upper('&&Schema'), 'FOO_YM_TX', 'FOO_TMP_YM_TX'
  );
  -- simulate updates of the table
  insert into &&Schema..foo
  select level + 1000, '2011' || substr(to_char(mod(level, 10) + 1, '00'), 2)
  , mod(level, 7)
  , rpad('*', 100 + mod(level, 900), '*')
  from dual
  connect by level <= 1000
  ;
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(upper('&&Schema'), 'FOO', 'FOO_TMP');
end;
/
timing stop
select dbms_metadata.get_ddl('INDEX', 'FOO_YM_TX', upper('&&Schema')) from dual
;
select count(*) as records, max(id) as max_id
from &&Schema..foo
;
alter table &&Schema..foo_tmp rename constraint foo_pk to foo_tmp_pk_old
;
alter table &&Schema..foo rename constraint foo_tmp_pk to foo_pk
;
select table_name, constraint_name, constraint_type
from all_constraints
where owner = upper('&&Schema')
order by 1,2
;

These two Oracle SQL scripts create a partitioned table with a local index NOCOMPRESS and convert the index to COMPRESS 1 by means of DBMS_REDEFINITION. (You cannot change compression this with ALTER INDEX.)