Does anyone know how to determine the last time an index was used?
We have a large SAP database (750 gb) with thousands of tables. Over the years many people have created countless indexes, some of which are probably obsolete.

Hi,

I doubt any such tracing been implemented at an Informix database server. Such tracing mechanism may take toll on performance should it active. I can suggest you a way, however it may take toll on you and your applications. You may think in terms of deactivating such indexes, which you may think of no use.

This definitely need expertise on the domain knowledge of the running applications, schema layouts, types of queries fired at, bare minimum index requirement at el. You identify an index that has least importance in the application and for the attached end-users. To deactivate use SQL:
SET INDEXES idx_i1 DISABLED;

Monitor the performance for some period, if you find adverse effect of this activity, force back the index with SQL:
SET INDEXES idx_i1 ENABLED;

Reactivating an object is much easier and more accurate than recreating it. If you do not need the index in question, you may goahead to drop it.

To see a list of deactivated indexes use SQL:
select * from sysobjstate where objtype='I' and state='D';

Regards,
Shriyan