Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / July 2005

Tip: Looking for answers? Try searching our database.

Table size estimation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
chettiar - 18 Jul 2005 15:34 GMT
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 - 18 Jul 2005 17:14 GMT
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

> Hi,
>
[quoted text clipped - 16 lines]
> Regards
> Sandip
chettiar - 31 Jul 2005 08:49 GMT
is there any exact formula to calculate the index space used.. i cant
figure out as of now.. some one please help
Mark A - 31 Jul 2005 09:29 GMT
> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.