Hello Tom,

Greetings to you!
My question to you is regarding rebuilding of the indexes when we have altered our table structure at a later stage.
We have an existing _P table whose structure needs to be altered a little, some new attributes (columns) need to be added now. So, for this we altered our table, replaced the existing synonyms and views as well.

But, there were some indexes already created on the table and the already created indexes use the attributes (columns) already present.
Since now, we have added 3 more attributes (columns) into our _P table, do we require to rebuild our existing indexes here?
I do not think of rebuilding as the indexes do not use the new attributes (columns) being added. But, I still have a doubt, would change in the table structure affect the indexes? Would the indexes get INVALID in any case due to table alteration?

Please provide your helpful feedback.

Thanks a lot!

Regards,
Himani Pant

create table t (
  c1 int, c2 varchar2(10)
);

create index i1 on t ( c1 );
create index i2 on t ( c2 );

insert into t values ( 1, 'c' );
commit;

alter table t 
  modify c2 varchar2(100);
  
alter table t
  add c3 int;
  
select index_name, status 
from   user_indexes
where  table_name = 'T';

INDEX_NAME    STATUS   
I1            VALID     
I2            VALID     

alter table t
  move;
  
select index_name, status 
from   user_indexes
where  table_name = 'T';

INDEX_NAME    STATUS     
I1            UNUSABLE    
I2            UNUSABLE