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?
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.