Problem solve Get help with specific problems with your technologies, process and projects.

Can there be too many columns in an index?

Is there such a thing as having too many columns in an index?

There can certainly be "too many" columns in an index. The problem is determining how many columns are "too many."

Each database engine has some small, finite limit on the number of columns it will support in an index. Most engines will support up to 15 columns in an index, but check the documentation for the database engine you are using.

There are also practical limits to how many columns a database engine can effectively support. Most applications of an index hit these limitations LONG before they hit the absolute limit the engine places on the number of columns in an index.

One factor that contributes to this practical limit is the width of the indexed columns in bytes versus the size of a database page. If you exceed some practical limit that is usually around ten percent, the benefit you gain from being able to search the index pages is offset by the I/O needed to search them so the process becomes self-defeating.

Another limit is how well the optimizer can use the indices that are available. Many optimizers can only consider the first (leftmost) column in an index. If that column isn't very selective, the chances of the engine choosing to use that index are very small which means that unless the application somehow coerces the engine into using an index, that index effectively doesn't exist.

There isn't a hard and fast rule that says "you can effectively use N columns in an index with database engine X." You have to look at how your application uses the engine, and if the index helps your overall performance, the index should be created and maintained. If the index hurts your overall performance, it should be dropped.

This leads to one other thing that you need to consider. Indexes have a cost in terms of disk, maintenance time and overall database complexity. There are times that an index might be very helpful for something like period (month, quarter, year) end processing, but otherwise it would be much less useful. If this is the case, you might consider building this kind of index only when you need it and dropping it when you are done.

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • The Best Database Design Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture