Can anyone tell me that if i drop an index , do i need to do runstats and reorg ? Do i also need to recompile the programs which were using the index ? What would happen if i drop the index , do no do the runstats and reorg but donot recompile the program.
[You should re-compile and rebind all of the programs that use the table. It is doubtful that you will know which queries may use that index.
I know which all programs use that index as i have explain reports. I have modified the program to avoid using that index. Actually , there is only 1 program which is using that index ( apparently this index is a secondary index ) and when the index was defined , the size of table was really small but now the size of the table is nearly 500 million times and any structure change causes a problem in the table as well as as Reorg also takes time.
so, is it fine if i recompile just the one program using that index ?[/quote]