Using Snapshot Monitoring data is it possible to know which indexes are being used by an application.

So also, can we identify the indexes not being used by an application.

Of couse, we can use visual explain on SQL statements to identify the index being used. This is not practically possible for an application with Dynamic SQL running into 1000s.

In Oracle 9i, we have a new feature called 'monitoring usage' for an index, which can be switched on / off.

TIA.

An index which has not been used up to now, can possibly be used intensively from tomorrow on, e.g. when the table size changes drastically, or actually when RUNSTATS is performed after such a change.

So I would say that one should rather look at the typical database use, when deciding to add or drop indexes: e.g., with a read-only database, add as many indexes as possible.

That sounds good in theory, but there rarely is a true read only database, since usually the data must be loaded. For large data loads, too many indexes can cause many problems.

Also, even in a true read only database, DB2 might spend more time evaluating the best access path for each query if there were more indexes than are ncecessary. So I would always suggest that one understand when an index will never be used by DB2 (primarily because of low cardinality) and never create those kind of indexes.

Agreed.

Usefulness of an index is normally easily deduced from the meaning of (& expected contents of & expected querying on) a certain column or set of columns.

Gathering statistics (with e.g. the "monitoring usage" tool of Oracle) could be a means of finding out about usefulness of indices, but most of the time the meta-level "understanding" of the tables is far more reliable in this respect.

do not forget the horrible probability of an unused index actually helping you get a good access path. What?? I hear you say...well we just got it here with DB2 z/OS 7.1. We created a "bad" index with terrible cluster ratio BUT with two columns that were the 2nd and 4th columns in another index. Using EXPLAIN with the bad index there (After RUNSTATS of course!) the first index was used and all was OK. After dropping the bad index an EXPLAIN showed that the table join order was different and the performance of the query was terrible! Reason: The "bad index" contained "extra" info that the Optimizer could use to see that the filter factor of the combined fields was good (This data would only be available in V7 when we use DSTATS). So the new answer for this old question is probably:-

There is *never* an unused DB2 index

Roy