Index query

[SSuhaib]

Hi,

Progress 9.1E, Solaris 5.10

One of our client's in-house devloped database has 60 indexes ( 60 !!!) for a table. Now they would like to disable some of them. One way of identifying non-used / least used indexes is to browse through all source code. But this approach is time consuming and tedious.

Is there any other way like, say, VST, Protop, Promon etc to list index names and their last usage by date accessed by the in-house application?

Thanks & Regards

 

[MaximMonin]

You can compile whole project with -XREF option and search for index-name inside ref files.

Or use _IndexStat table

for each _indexstat:
find _index where _idx-num = _indexstat-id.
display _index._index-name _indexstat-create
_indexstat-read _indexstat-delete.
end.

Change db server params
-baseindex n /* the first index' number for the range */
-indexrangesize n /* the number of indexes you want to
trace starting from -baseindex */
else youll see only first 50 indexes in DB

 

[TomBascom]

Basically you need to watch the application run for a while to determine which indexes are in use. ProTop is, of course, an excellent tool for this purpose

Keep in mind that the period that you watch may not include all possible application execution paths -- you might, for instance, miss indexes that only get used in the nightly batch run or at year-end.

I've done this kind of thing for clients from time to time. Usually we run a data collector (similar to what MaximMonin shows) for a week or so to identify candidate unused indexes. We then combine that with information culled from XREF to determine the status of apparently unused indexes that seem to be occasionally used -- a lot of the time XREF finds references in code and when you look at it with the programmers it turns out that that particular module has been obsolete for years. (Side note: I often find that 25% or more of an application's code base is "dead wood".)