In addition, in disk-based tables, the server requires 1 additional byte per column; this byte is used as part of the length indicator, which also serves as a null indicator.
The header for each row uses 12 bytes: 3 bytes for the row header, 3 bytes for the table id and 6 bytes for the row version.
If a disk-based table contains indexes other than the primary key, the size of the entries in those indexes must be estimated separately using the same guideline. An index entry contains the following components:
▪Columns that are defined in the index.
▪Columns of the primary key of the table.
▪A row header (12 bytes).
Additionally, there is usually some empty space (for example, 20 - 40%) in the database pages. This is why the formulas include a multiplier of 1.4 for both tables and indexes.
For example, first create a disk-based table as follows:
CREATE TABLE subscriber ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(50), salary FLOAT) STORE DISK;
Then create a secondary index as follows:
CREATE INDEX subscriber_idx_name ON subscriber (name);
The index entry contains the NAME column. It also contains the primary key column, which in this case is ID. The space required by that index should be estimated separately. The total size of the disk-based table, assuming the “empty space factor” is 1.4, can be calculated as follows:
rows x 1.4 // 1.4 = the empty space estimate. x ( (12 + 4 + (50+5) + 8 + 3) // size of the table entry, + (12 + 4 + (50+5) + 2) ) // size of the secondary index entry
This calculation can also be presented in the following way:
space required for space required for one row in table one row in index | | ----------------------- ------------------- | | | | | | | | rows x 1.4 x ((12 + 4 + (50+5) + 8 + 3) + (12 + 4 + (50+5) + 2)) | | | | | | | | | | | row header size <-- | | | | | | | | | | sizeofINT <------------ | | | | | | | | | sizeofVARCHAR(50) <--------- | | | | | | | | VARCHAR overhead <------------- | | | | | | | sizeofFLOAT <----------------------- | | | | | | length indicators (1 byte per col) <---- | | | | | row header size (in index) <------------------- | | | | size of INT <-------------------------------------- | | | size of VARCHAR(50) <----------------------------------- | | VARCHAR overhead <----------------------------------------- | length indicator bytes (1 per column) <-------------------------