437,610 Members | 1,677 Online Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

# Table size estimation

 P: n/a Hi, I am wanting to find out the size of a table for which I am using the following query: select c.card * b.columnlength from syscat.tables c, (select sum(a.avgcollen) as columnlength from syscat.columns a where a.tabname = ) b where c.tabname = and c.tabschema = ; My question: How can I use the Npages in the syscat.tables to arrive to the size of the table. How can i estimate the size of the indexes for a table. How can i find out the size of the allocated space for the table and index. Regards Sandip Nov 12 '05 #1
4 Replies

 P: n/a Unless you specified otherwise in the tablespace definition; each page of a table will occupy 4k of disk space. Multiply the number of pages by 4k and you have the physical space occupied. This space also includes freespace on the pages. The number of leaf pages for an index contains a count of all except 1 page for two level indexes. For three level indexes; you can make a guess by multiplying the number of leaf pages by a number between 1.0 and 1.2. This is a very crude technique. Sum up the number of pages for all of the indexes, add the space occupied by the data and you'll have your final answer. LOB obkects, stored separately from other table data, occupy additional space. Any technique that uses ...pages counts in the catalog is accurate only immediately after running runstats. If the statistics are stale, then the size estimates will be stale. There are also other ways of determining space. Philip Sherman chettiar wrote: Hi, I am wanting to find out the size of a table for which I am using the following query: select c.card * b.columnlength from syscat.tables c, (select sum(a.avgcollen) as columnlength from syscat.columns a where a.tabname = ) b where c.tabname = and c.tabschema = ; My question: How can I use the Npages in the syscat.tables to arrive to the size of the table. How can i estimate the size of the indexes for a table. How can i find out the size of the allocated space for the table and index. Regards Sandip Nov 12 '05 #2

 P: n/a is there any exact formula to calculate the index space used.. i cant figure out as of now.. some one please help Nov 12 '05 #3

 P: n/a "chettiar" wrote in message news:11**********************@g44g2000cwa.googlegr oups.com... is there any exact formula to calculate the index space used.. i cant figure out as of now.. some one please help Indexes need 9 bytes for each row + the size of the index columns + space for page overhead (100 bytes per page) + space for non-leaf pages (the b tree) + plus space for pctfree. The pctfree default is 10%. Pctfree is established when an index is created, or right after a reorg, but the empty space on an index page could grow as a result of page splits. The Administration Guide suggests the following formula as a rough estimate: (average index key size + 9) * number of rows * 2 See the manual if you want to calculate more precise numbers. Nov 12 '05 #4

 P: n/a is there any exact formula to calculate the index space used.. i cant figure out as of now.. some one please help Nov 12 '05 #5

### This discussion thread is closed

Replies have been disabled for this discussion. 