The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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
206152024
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: 30%

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
28.1k477636916
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:

×28
×27

question asked: 25 Mar '14, 09:41

question was seen: 1,528 times

last updated: 25 Mar '14, 16:05