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

asked 25 Mar '14, 09:41

pmiller's gravatar image

pmiller
206162024
accept rate: 37%

edited 25 Mar '14, 10:05


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
permanent link

answered 25 Mar '14, 11:21

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 32%

edited 25 Mar '14, 16:05

1

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

(25 Mar '14, 15:53) Breck Carter

Ah yes, thanks. Corrected

(25 Mar '14, 16:04) Mikel Rychliski

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

permanent link

answered 25 Mar '14, 15:50

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×32

question asked: 25 Mar '14, 09:41

question was seen: 3,773 times

last updated: 25 Mar '14, 16:05