I'm trying to follow these steps: Space estimation

to estimate the size of my table that I'm creating. I specified an INITIAL as 1M and using the steps above I have the following estimations:

Rowsize = rowheader  + sum of columns = 3 + 99 = 102
Rowspace = Rowsize + SB2 = 102 + 2 = 104
Num rows per block = FLOOR(7674/104) = 73
Number of blocks = 1000000/8192 = 122

However from here I wasn't sure what to do to finish estimating the table size as I had number of rows per block, row size and row space. I tried the following but I wasn't sure if it was correct:

table size = number of blocks * (number of rows per block * rowspace) = 122 * (73 * 104) = 926224 which is close to the INITIAL I set. I also wasn't sure if I should be even setting an initial and if the estimated table size should be the initial, but then I don't know where I would get the number of blocks from..

I have not created the table and I am trying to estimate it's size before creating it.

  • What are you trying to calculate exactly? If you set initial to 1M and don't load any rows, your table will consume 1M of storage, 100% of it empty (+/- some metadata). But that's pretty useless/trivial information. – Mat Aug 29 '15 at 9:50
  • trying to calculate/estimate the table size. I mentioned the INITIAL only because I could calculate the number of number of data blocks from it which I need in the last step to calculate the table size as I had calculated the number of rows per block and the row space but I didn't know how many data blocks there were – joe Aug 29 '15 at 10:13
  • Still don't understand what you're try to measure. You already have the table size (initial). If that's not what you want, what is it? – Mat Aug 29 '15 at 10:17
  • perhaps I'm not being clear, maybe the steps ("Estimating Space Required by Non-Clustered Tables") in the link I provided might make more sense than I do. – joe Aug 29 '15 at 10:22
  • I guess the thing you're missing is how many rows you plan to store in there. Otherwise, as I've said, if you just create the table you'll get INITIAL allocated and that's it. (Ignoring deferred segment creation.) – Mat Aug 29 '15 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.