2

I am working on an Oracle 12c database. I have a table being queried a lot. One query uses columns A and B in where clause, another query uses A,B,C,D & E columns in the where clause. I want to speed up queries.

I have added 2 non-unique indexes, one on A & B and one on A & B & C & D & E, so my second index actually includes the first index columns. Is this right? Technically wrong? Or it depends?

In such cases, in which a table is queried on different columns, what is the best practice for indexes? What are the impacts of indexes?

Mahdi Tahsildari
  • 13,065
  • 14
  • 55
  • 94

1 Answers1

3

In this case, there is no need for your index (A,B), given that you have an index(A,B,C,D,E). The query can effectively use the leading part of the index. Indexes will have an an impact on DML (index, update, delete) since indexes have to be maintained.

Having said that, it's possible that the optimizer will choose not to use the index and perform a full table scan, because it's cheaper - and that is normal, proper behavior. For example, if my table has 1 million rows, and my query returns 50% of them, it will probably be better to do a full table scan rather than use the index.

BobC
  • 4,208
  • 1
  • 12
  • 15
  • Thanks Bob. Regarding to "... can use the LEADING part of the index", what if my first index was C,D instead of A,B? Does the ABCDE index also cover C,D index or not? If I also want C,D index should it be declared as a seperate index? – Mahdi Tahsildari Feb 09 '17 at 14:40
  • If your index is on (A,B,C,D,E), and you where is on C,D, the optimizer *may* use a index skip scan operation. However, these are notoriously hard to cost accurately, and often do not perform well anyway. In that case, you may want an index on just (C,D). However, don't go crazy and start indexing every combination, as you will end up with index bloat !! – BobC Feb 09 '17 at 14:51
  • haha :) thanks Bob, I'm absolutely not going to blow the database with redundant indexes, I'm googling and studying optimization solutions in detail to achieve that. – Mahdi Tahsildari Feb 11 '17 at 05:19
  • @MahdiTahsildari Be cautious of "tuning by Google". There is some good information out there; but there is also a lot of complete nonsense. For the most part, Stack Overflow is a pretty good resource, so come back with new questions :) – BobC Feb 11 '17 at 05:48
  • Side-note.I was thinking about this edge case where C,D & E are "big" fields and you might only want to e.g. count occurrences of AB combinations. In that case the index on AB will be covering and since it only contains two "small" fields it will cause a lot less IO (and thus potentially better caching) than the ABCDE one. If you need to do that (very) often the cost of maintaining this 'superfluous index' might be offset by the gains of less IO while querying. As always, things depend a lot on the data and on the use case ... there hardly ever is a single answer that fits all situations. – deroby Feb 11 '17 at 21:14