On Fri, 21 Apr 2006, Rusty Conover wrote:
>Hi,
>
>Is there an easy way to get an estimate on the space used by an
>existing index? If not what is a good guess on how to estimate the
>size?
>
>My guess would be (assuming text fields are being indexed):
>
>[total length of all index keys] + [number of rows]*8 (for the offset
>location)
>
>Is that close? I realize disregards all space used by page allocation
>overhead.
The page overhead will be pretty constant per index entry.
One underestimate from above is the effect of overflow pages and internal
fragmentation. If the key doesn't fit in the btree node, overflow pages
are used to store the rest of the key, and the pages are used in their
entirety, and not shared with other entries. Thus, if your keys are quite
long, the internal fragmentation must be taken into account in the total
key length. If 1/4 of your rows cause overflow, the the extra overhead can
be approximated as:
pagesize/2 * [num of rows]/4
This assumes that the last overflow page is on average half full.
>
>Thanks,
>
>Rusty
>--
>Rusty Conover
>InfoGears Inc.
>
>
>
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
