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

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?

---

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.

---

Actually there can be a decrease in performance when you drop the index on the single column.

Suppose you do a query with WHERE [Column1] = 123. This could be solved perfectly with the original index. The new index can also be used for this but will -depending on the implementation- need to read the values for [Column2] in the index as well even though they are not used.

So yes: there can in theory be a downside to dropping the index: increased reads.

[Update 9 sept.]
Recently I encountered another situation where a single index can be much better than a combined index.
Consider a huge table 'bugs' with column [status], [createdate] and some other fields. Most bugs will be closed so assume status is '0' (open) for 100 records and '1' (closed) for 99000 records.

SELECT * FROM bugs WHERE status = '0' will benefit enormously from an index on status whereas with SELECT * FROM bugs WHERE status = '1' an index on status will not help.

Oracle will know the difference because it builds statistics on the index.

However, with an combined index on status, createdate each index entry is almost unique and Oracle will decide not to use the index for the query SELECT * FROM bugs WHERE status = '0' because it guesses (wrongly) that the index would not help.

So in this situation a single index should not be dropped just because it is a prefix to a combined index.

Note: In theory Oracle could build even smarter statistics on the index but it does not seem to do that.

---

The harm in not dropping the original index is the overhead Oracle has in maintaining both indexes when only one is needed.

The downside to leaving them is a drop in CRUD operations' performance on your table, I realise from your post that this may be "insignificant" at the moment but tables grow over time and in the future this could cause you problems that would then have to be remediated.

It will also take up more storage unnecessarily.

Your previous procedures will still be able to use the new index too.

Leaving unnecessary indexes will confuse future developers and DBA's that have to support your database costing them time and effort to investigate why the duplicate index exists.

Rather than looking for reasons not to drop the original index, I'd be looking for reasons to keep it.

Drop it, test your "other" procedures' performance and you should see little difference, if there is a problem you can investigate why and if necessary replace it.