How to Tell When an Index Reorg is Needed

You may also like...

8 Responses

  1. Suvradeep Sensarma says:

    This is another wonderful write up by you. Thanks for sharing. Is there any way to find directly the size of an index ?

    • Ember Crooks says:

      In my mind, the easiest, quickest way is still to use reorgchk or one of the related functions. Using DMS or AST tablespaces, you cannot directly look at a file on disk to see the size if that’s what you’re asking.

  2. adriana lopes says:

    How can I calculate the nleaf (number of active leaf pages in the index)?
    I really need this information to create virtual indexes.
    Thank you.

    • Ember Crooks says:

      SYSCAT.INDEXES shows nleaf. It is populated when you do runstats on indexes. You can query it using something like this:

      db2 “select substr(indschema,1,10) as indschema, substr(indname,1,30) as indname, nleaf from syscat.indexes”

  3. Nadir Doctor says:

    Here is some sample sql which will provide a list of top 10 indexes based on size which are greater than 100 mb –

    SELECT Substr(indschema,1,6) AS indschema,
    Substr(indname,1,18) AS indname,
    indextype,
    Substr(tabschema,1,6) AS tabschema,
    Substr(tabname,1,18) AS tabname,
    avgleafkeysize,
    avgnleafkeysize,
    indcard,
    and int((avgleafkeysize + 11)*indcard*2/(1024*1024)) AS space_mb,
    compression,
    lastused
    FROM syscat.indexes
    WHERE tabschema NOT LIKE ‘SYS%’
    AND int((avgleafkeysize + 11)*indcard*2/(1024*1024)) > 102400
    ORDER BY 9 desc
    FETCH first 10 ROWS only WITH UR;

  4. Nadir Doctor says:

    Explanation for formula used is available on webpage below and estimate is good for db2 9.7 as well:

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0004929.html?lang=en

  5. Umesh says:

    Hi Nadir,

    SQL is not working,please check the syntax.

  1. June 17, 2014

    […] How to Tell When an Index Reorg is Needed […]

Leave a Reply

Your email address will not be published. Required fields are marked *