Re: Calculate Index Size problems
I don't mean to be unhelpful - because, straight up, I don't know the
answer.
But I'm really interested to know why anyone ever bothers to do this.
I can remember in every DBMS course I've ever done we've done endless
tedious space calculation exercises. And never used it - not since the
invention of disks bigger than 512M anyway.
Just guess and then triple it - that's my advice!
"Jay" <remove:jay@td.ca> wrote in message
news:X_zPa.9672$ru2.1027767@news20.bellglobal.com...
>
>
> Hi,
>
> I'm trying to calculate the number of pages a index takes up, but keep
> coming up short. The table I'm woring with was just created (i also
altered
> it to cluster),
>
>
> oncheck output
>
>
> TBLspace Report for test:pen.acc_bal
>
> Physical Address a009a4
> Creation date 07/11/03 09:50:05
> TBLspace Flags 801 Page Locking
> TBLspace use 4 bit bit-maps
> Maximum row size 71
> Number of special columns 0
> Number of keys 1
> Number of extents 2
> Current serial value 1
> First extent size 8
> Next extent size 8
> Number of pages allocated 2120
> Number of pages used 2117
> Number of data pages 1827
> Number of rows 98619
> Partition partnum 5243161
> Partition lockid 5243161
>
>
> Extents
> Logical Page Physical Page Size
> 0 a250af 1280
> 1280 a255b7 840
>
> TBLspace Usage Report for test:pen.acc_bal
>
>
> Type Pages Empty Semi-Full Full
Very-Full
> ---------------- ---------- ---------- ---------- ---------- ---------
-
> Free 5
> Bit-Map 1
> Index 287
> Data (Home) 1827
> ----------
> Total Pages 2120
>
> Unused Space Summary
>
> Unused data slots 39
> Unused bytes per data page 18
> Total unused bytes in data pages 32886
>
>
> Home Data Page Version Summary
>
> Version Count
>
> 0 (current) 1827
>
> Index Usage Report for index accbal_idx on test:pen.acc_bal
>
>
> Average Average
> Level Total No. Keys Free Bytes
> ----- -------- -------- ----------
> 1 1 286 1084
> 2 286 344 1827
> ----- -------- -------- ----------
> Total 287 344 1824
>
> I have used the following to calculate the index size
>
> 1.. Key value = (SUM(columns) + 4) * 1.5
> 2.. propunique = nrows / nunique
> 3.. entry size =(length of row pointer * average number of rows per
unique
> index) + key value
> 4.. # of index entries per page = 4068 / entry size
> 5.. Estimated # of leave pages = # of index entries per page / nunique
> 6.. Add 5 percent for branch and root nodes
>
> 1.. (SUM(4)+4)*1.5 = 12
> 2.. 98619 / 18359 = 5.37 -- got nunique from sysindexes
> 3.. (5 * 5.37) + 12 = 38.85
> 4.. 4068 / 38.85 = 104.71
> 5.. 18359 / 105 = 174
> 6.. 174 * 1.5 = 183
>
> 183 is quite off from onchecks 287. Can anyone point me in the right
> direction?
>
> Thank you
>
>
>
> J
>
>