Which query i need to get the size of an index in Sybase SQLAnywhere 10 or 16? |
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 |
Foxhound uses SYSPHYSIDX.leaf_page_count as a pretty good approximation for the purposes of identifying Tables From Hell and so on :) |