List idexes by their size in Db2 database

Query below returns list of indexes in a database with space they use ordered from the ones using most.


select indschema as index_schema,
       indname as index_name,
       tabschema || '.' || tabname as table,
       index_object_l_size as logical_size,
       index_object_p_size as physical_size
from table(sysproc.admin_get_index_info('I','',''))
where indschema not like 'SYS%'
order by physical_size,


  • index_schema - schema name
  • index_name - index name
  • table - table name on which index is defined
  • logical_size - logical space used by index. The logical size is the amount of space that the table or data partition knows about.
  • physical_size - physical space used by index


  • One row represents one index in a database
  • Scope of rows: all indexes in a database
  • Ordered by index used size from largest to smallest, table schema and table name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.