Is it necessary to consider dropping the existing index because it is a prefix of the recommended index

Question

SQL Tuning Advisor of Oracle SQL Developer v3 suggests the following for my query:

Consider running the Access Advisor to improve the physical schema design or creating the recommended index. If you choose to create the recommended index, consider dropping the index "SCHEMANAME"."INDEXNAME" (on "COLUMN1") because it is a prefix of the recommended index.

create index SCHEMANAME.NEW_INDEXNAME on SCHEMANAME.TABLENAME("COLUMN1","COLUMN2");

Is there any harm in not doing suggestion in bold? The problem is that the existing index it suggests to drop is used by other procedures. I didn't think the idexes could "harm" each other, is there any downside of leaving both indexes apart from the disk space they will take and an insignificant performance decline on insert/update?

Answer 1

So, assuming OLD INDEX is on [Column1] and RECOMMENDED INDEX is on [Column1][Column2], the recommended index can be used for the existing queries as well.

In short: removing the OLD INDEX will, as you said, increase performance on insert/update, and also will not decrease the ability to seek over scan on the queries that were using OLD INDEX. RECOMMENDED INDEX Still allows a seek for [Column1] values, as well as [Column1][Column2] values.

So there is no harm besides the performance drop on update/insert and the additional storage overhead, but there is also no gain to maintaining both indices.