Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: obsolete indexes

    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.

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •