sqldatabaseoracle-databaseindexingdatabase-performance

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

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!

Solution

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