Calculating storage requirements for disk-based tables
The general formula for the space required for a disk-based table is:
chkpt_factor x (table_size + sum_of(index_sizes))
where:
▪chkpt_factor is between 1.0 and 3.0 (explained below)
▪table_size = 1.4 x rows x (sum_of(col_size + 1) + 12)
▪rows is the number of rows
▪sum_of(col_size + 1) is the sum of the sizes of the columns plus one byte per column. The column sizes are shown in a table later.
▪For each disk-based index, the index_sizeis 1.4 x rows x (pkey_size + idx_size), where pkey_sizeis the sum of the sizes of the columns in the primary key, and idx_sizeis the sum of the sizes of the columns in the index.
The chkpt_factoris needed to take into account that “checkpoint” operations may briefly require up to three times the size of the database. During a checkpoint operation, a copy of each of the changed pages in the database is copied from memory to the disk. If every page in the database has been updated, then it is possible to copy as many pages from memory as there already are on disk.
Furthermore, the most recent successful checkpoint is not deleted until the current checkpoint is successfully completed. Therefore, during a checkpoint the disk can simultaneously have up to 3 copies of each page (1 copy for the page in the database, 1 copy in the most recent successful checkpoint, and 1 copy for the current checkpoint while it is executing). The checkpoint factor therefore can be between 1.0 and 3.0. Values approaching 3.0 are rare in most databases. A value of 1.5 is usually well sufficient even for small databases that have high levels of activity. The less frequent the checkpoint, the larger the chkpt_factormight need to be.
Note In a disk-based index, if you do not explicitly define a primary key, the server uses a server-generated “row number” as the primary key. This forces the primary key index to store records in the same order that they were inserted.