How can i get the size of an index in SQL Anywhere 10 or 16?

[Former Member]

Which query i need to get the size of an index in Sybase SQLAnywhere 10 or 16?

[former_SQLA_member1694878 · Accepted solution]

You can do this by counting the number set bits in the allocation bitmap of the SYSPHYSIDX system view. This query doesn't take into account the fact that some of the index pages may be only partially full.

SELECT T.table_name, I.index_name, (DB_PROPERTY( 'PageSize' )*count_set_bits(p.allocation_bitmap))/1024 as "Usage (KB)"
FROM   sys.sysidx AS i
       JOIN sys.systab AS t
         ON T.table_id = I.table_id 
       JOIN sys.sysphysidx AS p
         ON p.table_id = i.table_id 
           AND  p.phys_index_id = i.phys_index_id 
ORDER BY "Usage (KB)" DESC

[Breck_Carter · Accepted solution]

That would be "Usage (KB)" methinks... PageSize is in bytes.

[former_SQLA_member1694878 · Accepted solution]

Ah yes, thanks. Corrected

[Breck_Carter]

Foxhound uses SYSPHYSIDX.leaf_page_count as a pretty good approximation for the purposes of identifying Tables From Hell and so on 🙂