Estimate the size of the table
The following steps can be used to estimate the amount of space required to be required in the storage table.
Specify the number of rows in the table:
Number of rows in the table = Num_Rows
If there is a fixed length and variable length column in the definition of the table, the space occupied by each of the two sets of columns in the data line is calculated. The size of the column depends on the data type and length description. For more information, see the data type.
Columns = Num_cols
All bytes in all fixed length columns = fixed_data_size
Variable length column = Num_variable_cols
Maximum value of all variable length columns = max_var_size
If there is a fixed length column in the table, a part of the row (called a space map) will be retained to manage the column. Calculate the size:
Space map (null_bitmap) = 2 ((Num_COLS 7) / 8)
Use only the integer portions in the above expressions to remove the rest.
If there is a variable length column in the table, make sure to store these columns in the row to be used:
Variable length column total size (variable_data_size) = 2 (Num_Variable_cols x 2) max_var_size
If there is no variable length column, set the variable_data_size to 0.
This formula assumes that all variable length columns are full of 100%. If the proportion of storage space occupied by the expected variable length column is low, the result can be adjusted in accordance with this ratio to give a more accurate estimate of the entire table.
Calculate line size:
Total size (row_size) = fixed_data_size variable_data_size null_bitmap 4
The last value 4 represents the head structure of the data.
Next, calculate the number of rows per page (8096 available bytes per page):
The number of rows per page (rows_per_page) = (8096) / (row_size 2)
Because the line does not cross the page, the number of rows per page should be adjacent to the nearest integer.
If you want to create a gathering index on the table, then calculate the number of available rows left per page based on the specified fill factor. For more information, see the Fill Factor. If you do not create a gathering index, specify Fill_Factor as 100.
Number of available rows per page (free_rows_per_page) = 8096 x ((100 - Fill_Factor) / 100) / (row_size 2)
The filling factor used in the calculation is an integer value, not a percentage.
Because the line does not cross the page, the number of rows per page should be rounded down to the closest integer. When the fill factor is increased, more data will be stored per page, so the number of pages will be reduced.
Calculate the number of pages needed to store all rows:
Page (NUM_PAGES) = Num_ROWS / (Rows_Per_Page - Free_ROWS_PER_PAGE)
The estimated pages should be rounded up to the closest integer.
Finally, the amount of space required to calculate the data in the storage table (the total byte per page is 8192):
Table size (bytes) = 8192 x Num_pages
