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.