Welcome to our
on-going series of misunderstood issues in the world of DB2 for z/OS. Today’s
topic is indexing, or to be more precise: not indexing. What do I mean by that?
Well, I’ve heard folks
say that you should always create at least one index on every DB2 table. And
while I can empathize with that general notion, I can’t agree completely
because of that word “always,” which almost always makes a sentence wrong.
Sure, there are
several good arguments for creating indexes on DB2 tables. Perhaps the most
important one is clustering. DB2 uses an index to control how data is clustered
on disk. Only one index can be specified as the clustering index (because, of
course, the data on disk can only be stored in one order, right!). If you do
not specify a clustering index then DB2 will use the earliest created (oldest)
index to cluster the data. So it is usually a good idea to create an index to
guide clustering.
Another popular reason
to create an index on a DB2 table is to enforce uniqueness for a UNIQUE
constraint or PRIMARY KEY. The only way to enforce uniqueness on a DB2 column
(or set of columns) is by using a unique index.
Of course, there are a
lot of other good reasons to create indexes, most of them to improve query
performance. But I contend that there are situations when it makes sense not to
create any indexes at all.
So when does it make more sense
not to build an index for a DB2 table?
Let's
start by saying that most of the time you will want to build at least one - and
probably multiple - indexes on each table that you create. Indexes are crucial
for optimizing performance of SQL access. Without an index, queries must scan
every row of the table to come up with a result. And that can be very slow.
Having
said that, here are a few situations some times where it can make sense to have
no indexes defined on a table:
When all (or most) 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 diminish, not
enhance performance. Though not extremely common, you may indeed come across
such tables in your organization.
For a very small table with only a few pages of data and no
primary key or uniqueness requirements. A very small table (perhaps 20 to 30 or
so pages) might not need an index because simply reading all of the pages is
very efficient already.
When performance doesn't matter and the table is only accessed
very infrequently. But, hey, when do you ever
have that type of requirement in the real world?
Other
than for these circumstances, you will most likely want to build one or more
indexes on each table, not only to optimize performance, but also to ensure
uniqueness, to support referential integrity, and to drive data clustering.
Of
course, indexes do not come without cost. Indexes take up disk space and adding
a lot of indexes will consume disk space. An additional consideration is their
impact in data modification. Although indexes speed up queries they degrade
inserts and deletes, as well as any modification to indexed columns.
What do you think? Are there other situations where a table should have no
indexes? Are there any pertinent high-level issues I missed? Feel free to add
your thoughts and comments below!
