Table size estimation

[chettiar]

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

[Phil Sherman]

Re: Table size estimation

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:[color=blue]
> 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
>[/color]

[chettiar]

Re: Table size estimation

is there any exact formula to calculate the index space used.. i cant
figure out as of now.. some one please help

[Mark A]

Re: Table size estimation

"chettiar" <chettiarsandip @yahoo.com> wrote in message
news:1122796177 .102599.154590@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> is there any exact formula to calculate the index space used.. i cant
> figure out as of now.. some one please help
>[/color]
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.

[chettiar]

Re: Table size estimation

is there any exact formula to calculate the index space used.. i cant
figure out as of now.. some one please help