By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
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 = <colname>) b
where c.tabname = <colname>
and c.tabschema = <schemaname>;

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
Share this Question
Share on Google+
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 = <colname>) b
where c.tabname = <colname>
and c.tabschema = <schemaname>;

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" <ch************@yahoo.com> 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.