Should I index individual columns that are already part of a composite index?

Question

I have a composite unique index across 4 columns in my table and a query with performance problems that filters on 2 of the columns individually.

Perhaps a dumb question, but should I index the individual columns as well to improve performance here?

Thanks for any help!

Tavares

show your index schema and your query please – Hoe

Answer 1

I am answering this question because Joe's answer is not correct.

Oracle 9 introduced a new type of index scan, called the skip scan. This allows an index to be used for non-leading columns. The details are explained in the documentation.

It is true that in most databases, under most circumstances, indexes are used from the left-to-right. However, Oracle's skip-scanning mechanism is an exception to this, and an enhancement over indexing algorithms in other databases.

Spirt

Answer 2

The answer depends on which two columns. A composite index can be used for a subset of the leftmost columns. If your index is on (A, B, C, D) and you're filtering on A and B, you're good to go. If, on the other hand, you're filtering on B and C, then this index won't help and you need to create a new one.

Aloise

Thanks for the reply. To make sure I understand correctly, say the columns I'm filtering on were A and C. In that case, I would only want to add an index to C, right? – Tavares

@TheGilbertArenasDagger In the case of A and C, the existing (A,B,C,D) index could be used for the A filter, but will be of no help with C since it comes after B. You'd be better off creating an (A,C) index to fully support that query pattern. – Aloise

Answer 3

You deal with trade offs. Smaller indexes are faster. Lets try just two columns in an example. Say I have table X with character column A and B.

No lets say I create an index of A+B and another of B+A.

This allows me to use an index for just A or just B. But in reading the index the sql engine must read more total data volume of A+B or B+A. So this is slower than if you just had one for A or just one for B. So why not put in 4 indexes. Well you can do that but now you slow down your inserts because you have to maintain 4 different indexes.

There is not exact right answer. Other things affect differences. Which Sql engine you use, which version of a vendors sql engine. Understand the basic dynamics of the advantages and disadvantages of these actions. Combine that with a better understanding of your data set and those using your database. For example if you rarely have more than three B records for every given A record. It likely won't help much to have a composite index. It would be faster for the engine to use the index to find the three records then select among them which one you want to return.

Your results may vary. But this is very much a situation of engineering trade offs.

Remodel