Do I need to consider the existing index because it is a prefix of the recommended index

Question

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

Consider starting an Access Advisor to improve your physical design or create a recommended index. If you decide to create, it is recommended that you specify the SCHEMANAME index . "INDEXNAME" (at "COLUMN1") because it is the recommended index prefix.

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

Is there any harm in not doing it in bold? The problem is that the existing index, which it proposes to reject, is used by other procedures. I did not think that the ides could “harm” each other, is there a drawback in leaving both indices separate from the disk space they occupy and a slight decrease in performance when inserting / updating?

Answer 1

So, if OLD INDEX is in [Column1] and RECOMMENDED INDEX is in [Column1] [Column2], the recommended index can also be used for existing queries.

In short: deleting OLD INDEX will, as you said, improve insert / update performance , and will not reduce the search ability compared to queries using OLD INDEX. RECOMMENDED INDEX Still allows you to search for [Column1] values ​​as well as [Column1] [Column2] values.

Thus, there is no harm besides performance degradation during update / insertion and additional overhead, but it is also supported without amplification to support both indices.

Answer 2

In fact, there may be a decrease in performance when dropping an index in a single column.

Suppose you execute a query with WHERE [Column1] = 123 . This could be resolved with the original index. A new index can also be used for this, but there will be - depending on the implementation - the need to read the values ​​for [Column2] in the index, even if they are not used.

So yes: it could theoretically be a drawback for lowering the index: increased reading.

[Update 9 sessions]

I recently came across a different situation where one index can be much better than a combined index.
Consider the huge error tables with the [status], [createdate] columns and some other fields. Most errors will be closed, so suppose the status is “0” (open) for 100 entries and “1” (closed) for 99,000 entries.

SELECT * FROM bugs WHERE status = '0' will greatly benefit from the index on status , while with SELECT * FROM bugs WHERE status = '1' index on status will not help.

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

However, with a combined index on status, createdate each index entry is almost unique, and Oracle decides not to use the index to query SELECT * FROM bugs WHERE status = '0' because it guesses (by mistake) that the index did not help.

Thus, in this situation, one index should not be discarded just because it is a prefix of the combined index.

Note. Theoretically, Oracle could build even smarter index statistics, but it doesn't seem to.

Answer 3

The reason for abandoning the original index is that Oracle must support both indexes when only one is required.

The disadvantage of leaving them is a decrease in the CRUD performance on your table, I understand from your message that this may be "insignificant" at the moment, but the tables grow over time, and in the future this can cause problems, which then will need to be fixed.

It will also require more memory unnecessarily.

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

Having left unnecessary indexes, confuse future database developers and administrators who must maintain your database, spending time and effort on them to find out why a duplicate index exists.

Instead of looking for reasons not to drop the original index, I would look for reasons to keep it.

Drop it, check your “other” procedures, and you will see a slight difference, if there is a problem, you can investigate the cause and, if necessary, replace it.