Hi

Does anyone know a method of analysing how often a index has been utilitised? We have a couple of tables which have gradually become a little over indexed and we wish to determine which can be eliminated as not being used.

This is on DB2/UDB 7.2 fp7 on AIX

Any thoughts?

Thanks
Jamie

It's a little difficult for dynamic SQL statements (but not impossible). You can determine which indexes are used and then determine which are not used.

For static SQL, any index not attached to a package (as reflected in the DB2 catalog) is not used for SQL statements that are bound (with the bind command) using the DBRM output from the pre-compile of an application program.

But a good rule of thumb is to eliminate any index (except those defined as clustered) where the number of distinct values is less than the number of rows per page of the table.

So if the row size of the table is about 200, then there are about 20 data rows per 4K page in the table. Thus any non-clustered index with less than 20 distinct values probably will not be used by DB2.

This assumes however, that there is a fairly random distribution of distinct values of the index. If the distribution of index values is very highly skewed, then it might good to keep the index in certain cases because it may be used (if detailed table and index statistics are captured during runstats).

But my experience is that DBA’s and/or developers who create too many indexes, don’t understand clustering, so that needs to be examined at the same time.

Thus any non-clustered index with less than 20 distinct values probably will not be used by DB2.
---- why is this true?
And doesn't it have some connection with num_quantiles and num_freqvalues?

Yes, if the data is highly skewed (and one captures detailed stats) then it does depend on things like num_quantiles and num_freqvalues.

But the purpose of using an index is to avoid reading a data page on the table. In order to avoid reading a 4K data page, DB2 would need to be fairly sure that it didn't need any of the rows on that page. If there are 20 rows per page, then the number of unique values of the index should be greater than 20 (if the data is not skewed).

But here are some common examples of poor indexes:

- Division code or company code (when there are only a few divisions or companies being used).
- Sex (male or female) - this may be obvious, but I saw an index on sex once.
- etc.

If you have less than 20 distinct values the data needs to be skewed and one needs to search on the values that are not common for DB2 to use the index.

I am talking about the number of distinct values in the entire index, not just any one column of an index (if the index has multiple columns).

You can use the following query for some basic analysis of index statistics


SELECT
indname,
npages,
card,
firstkeycard as firstk,
first2keycard as f2key,
first3keycard as f3key,
first4keycard as f4key,
fullkeycard as fullkey,
nleaf,
nlevels as nlev,
clusterratio as cr,
clusterfactor as cf,
uniquerule as u,
t.colcount as tbcol,
i.colcount as ixcol
FROM syscat.tables t,
syscat.indexes i
WHERE i.indschema = t.tabschema
AND t.tabname = i.tabname
ORDER by card desc,
indname;