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

When not to have an index

When does it make more sense not to build an index for a DB2 table?

Well, that is a very open-ended question so I will give a very high-level answer. Here are some times when it might makes sense to have no indexes defined on a DB2 table:

  1. When all accesses retrieve every row of the table. Because every row will be retrieved every time you want to use the table an index (if used) would just add extra I/O and would decrease, not enhance performance.
  2. For a very small table with only a few pages of data and no primary/unique key requirements. A very small table (I'd say a maximum of 10 pages) might not need an index because simply reading all of the pages is very efficient already.
  3. When performance doesn't matter and the table is only accessed very infrequently.
Other than that, in most cases, you will want to build one or more indexes on each DB2 table to ensure uniqueness, support referential integrity, and to enhance the performance of SQL queries.

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.