Sonali,

Actually a better idea might be to rebuild your indexes like this :

index 1 - res_entity_type
index 2 - res_level
index4 - res_status, res_int_ext8

I did not look closesly enough the first time.

Oracle can use 2 indexes at once, and since you have index 3 being essentially index1 + index2, yes I agree that you don't need it. Also, I think you can drop the last column off index 4 since it is already indexed in index1.

I am assuming that your tables are analyzed and that you are using the cost based optimizer.

-John