Finding the Size of Individual Indexes

Recently I had a client ask me if there was a way to calculate the size of individual indexes on a table and how to interpret what is reported by the table function sysproc.admin_get_index_info. The client asked whether the value is reported as a sum per table or per individual index.

To explain this, I ran the following queries:

SELECT 
   IID, 
   index_object_p_size , 
   char(indname,30) 
FROM 
   table(sysproc.admin_get_index_info('T','DB2INST1','ABC_ITEM'))

IID    INDEX_OBJECT_P_SIZE  3
------ -------------------- ------------------------------
     1                 8704 MN_STOREID
     2                 8704 MN_ACCOUNT
     3                 8704 MN_ITEMSTATUS
     4                 8704 MN_STATUS
     5                 8704 MN_COL
     6                 8704 IDX80000
     7                 8704 MNITEM
 7 record(s) selected.

The table function reports the same index size for each of the indexes on this table, indicating that the size is the total space required for all indexes, not each individual index.

If you run the same query for an individual index, you will get the same value for INDEX_OBJECT_P_SIZE:

SELECT 
   IID, 
   index_object_p_size , 
   char(indname,30) 
FROM 
   table(sysproc.admin_get_index_info('I','DB2JCXP','IDX80000'))

 IID    INDEX_OBJECT_P_SIZE  3
------ -------------------- ------------------------------
     6                 8704 IDX80000

  1 record(s) selected.

Next, I ran this query to calculate individual index sizes:

SELECT 
   sum(nleaf*4) over (partition by iid order by iid) size_nleaf_kb, 
   char(indname, 30) indname 
FROM 
   syscat.indexes 
WHERE 
   tabschema = 'DB2INST1' 
   AND tabname = 'ABC_ITEM' 
ORDER BY 
   iid

The query returns the following individual sizes for the indexes:

SIZE_NLEAF_KB        INDNAME
-------------------- ------------------------------
                 632 MN_STOREID
                2800 MN_ACCOUNT
                 596 MN_ITEMSTATUS
                 600 MN_STATUS
                 564 MN_COL
                2124 IDX80000
                1188 MNITEM

  7 record(s) selected.

Some key considerations with the above query:

• Estimating the size of the index using NLEAF (the number of pages within the index) is the best available way as the leaf nodes within the index contain pointers to the actual data. This depends on having up-to-date statistics on your indexes, so you may need to use RUNSTATS prior to running this query. One potential issue with using NLEAF is on DPF systems, where NLEAF is extrapolated from the statistics for a single database partition, so this method is suitable for single-partition environments.
• The multiplication of NLEAF is with the PAGE SIZE of the tablespace, in kilobytes (4 in this case).

If I add these up, I get 8504 kb, which is 97.7% the 8704 kb reported by ADMIN_GET_INDEX_INFO. The remaining 2.3% is attributable to the root and intermediate nodes of the index, but this is such a small amount that the numbers shown above provide a fairly accurate indication of the size of each index.