6

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?

Ruslan
  • 9,927
  • 15
  • 55
  • 89

3 Answers3

8

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.

Sam DeHaan
  • 10,246
  • 2
  • 40
  • 48
  • 2
    +1 but perhaps you could also add the downside of extra storage space? – Tom H Aug 16 '11 at 14:11
  • 1
    spot on! old index is on column1. Well, if removing it won't harm filtering on column1, than why don't we always create one big fat index for all columns we query on? – Ruslan Aug 16 '11 at 14:18
  • 1
    @tsar An Index on data creates copies of the data in a different data structure (B-tree in SQL Server, unsure in other RDBMS). If a non-clustered index, it will copy only those fields (Column1/Column2 in this case) and then a reference to look up the rest of that record's data in the full table. A clustered index stores the entire dataset in the additional data structure. This means *a lot* of data overhead, and a lot of processing to balance the data structure on insert/update. – Sam DeHaan Aug 16 '11 at 14:22
  • If you're interested in a bit more theory on indexing related to this, I'd recommend researching 'Covering Indexes'. – Sam DeHaan Aug 16 '11 at 14:25
  • @Sam DeHaan, Oracle creates it's "standard" indexes in a B-Tree format unless specified otherwise. – Ollie Aug 16 '11 at 14:25
  • 1
    thanks all, found the answer to my comment here: http://stackoverflow.com/questions/179085/ – Ruslan Aug 16 '11 at 14:27
  • @Ollie thanks for the info. Haven't had a whole lot of experience in-depth outside of SQL Server, always nice to expand my knowledge. – Sam DeHaan Aug 16 '11 at 14:27
  • 1
    BTW, having an index on [Column1][Column2] will *not* enforce any ordering! Only an ORDER BY statement enforces ordering, anything else is coincidence and may change in any future version of Oracle! – Jeff Aug 16 '11 at 20:00
6

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.

Jeff
  • 736
  • 5
  • 14
  • Is there a link to this in any Oracle documentation? What sort of performance drop would be expected? – Ollie Aug 16 '11 at 21:05
  • 3
    Don't know of any documentation, just my interpretation of how I understand how indexes work. Having two columns in the index makes the total index twice as large. When the index is very selective (or unique) Oracle will usually only need to read a few 'blocks' to get all the information it needs. When the index is twice as large there is a chance it needs to read more blocks. However, I think the effect is negligible in all practical cases. – Jeff Aug 16 '11 at 21:10
  • 4
    +1. I thought this was obvious, I'm surprised you haven't gotten more votes. If your index is bigger, and contains information that not every query will need, then certainly there will be a performance penalty in some cases. – Jon Heller Aug 17 '11 at 03:39
  • 1
    As mentioned in my answer, always test the impact of your changes. I tested this myself as I was curious as to the impact in real terms. Here are the results (flushing buffer cache between tests, 5m records): With non unique columns, no index: 3.240s, col1 index: 0.025s, col1&2 index: 0.494s. With unique columns, no index: 1.962s, col1 index: 0.004s, col1&2 index: 0.391s. Over 5m records there was an impact of not having the specific col1 index so considering the number of records and comparing to your database it may or may not be noticed in your application. – Ollie Aug 17 '11 at 10:40
  • BTW, my queston about documentation was hoping that Oracle might have offered some method for working out the performace degredation rather than just having to directly test the impact. :-) – Ollie Aug 17 '11 at 10:42
4

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.

Ollie
  • 17,058
  • 7
  • 48
  • 59