The Row Size Calculation form is used to estimate the size of your tables in bytes. For details about computing row sizes, see Database-Level Capacity Planning Considerations.
Sources for the Required Information
The following table points to the sources for the miscellaneous column-level data requested by the Row Size Calculation form.
| Information | Source |
|---|---|
| Column name | Column name from your logical data model. |
| Type | Table form reference to the Constraint form. |
| Max | See Data Type Considerations. |
| Avg | For variable length types, must be computed; otherwise, use the value for Max. |
Things To Consider When Filling Out the Row Size Calculation Form
- The size of the row ID and row header depends on the table partitioning.
| Table | Row ID Size (bytes) | Row Header Size (bytes) |
|---|---|---|
| not partitioned | 8 | 12 |
| up to 15 partition levels (65,535 combined partitions) 2-bytes used for partition level number in row ID portion of row header |
10 | 14 |
| up to 62 partition levels (more than 65,535 combined partitions) 8-bytes used for partition level number in row ID portion of row header |
16 | 20 |
- BLOB, CLOB, and XML column values are stored outside the row.
The value you should enter for BLOB, CLOB, and XML columns in the Row Size Calculation form is their Object Identifier, which is always 39 bytes.
The size of each XML, BLOB, or CLOB subtable must be calculated separately (for more information, see Database-Level Capacity Planning Considerations).
- The following system-derived columns consume the following amount of space per row, respectively. Note that the first ROWID can only occur in a join index, and the second two only in result rows.
System-Derived Column Name Data Type Number of Bytes Stored for the Internal Information From Which They Are Derived ROWID BYTE - 10 for 2-byte partitioning or no partitioning
- 16 for 8-byte partitioning
PARTITION INTEGER 4 for 2-byte partitioning BIGINT 8 for 8-byte partitioning PARTITION#Ln INTEGER 4 for 2-byte partitioning BIGINT 8 for 8-byte partitioning - The following system-generated columns consume the following amount of space per row, respectively.
System-Generated Column Type Data Type Number of Bytes Stored Object Identifier (OID) VARBYTE 39 Identity Any of the following - BIGINT
- BYTEINT
- DECIMAL(n,0)
- INTEGER
- NUMERIC(n,0)
- NUMBER(n,0)
You can only use the fixed form of NUMBER for identity columns. The floating form is not valid.
- SMALLINT
1 - 8, depending on the data type. This is true even when the DBS Control parameter MaxDecimal is set to 38, because the values generated by an identity column are restricted to a maximum of DECIMAL(18,0) NUMERIC(18,0) or NUMBER(18,0).
- Compressed values and nulls have no effect on the length of their field in a column (they are “stored” as 0 bytes for the field), but can add to the row overhead because of their effect on the number of presence bits in the row header (for more information, see Presence Bits).
Be aware that each compressed value consumes space in the table header.
- The ending timestamp values for any PERIOD(TIMESTAMP) or PERIOD(TIMESTAMP WITH TIME ZONE) values stored with an UNTIL_CHANGED ending element value consume only 1 byte of storage for their ending element, while PERIOD(TIMESTAMP) and PERIOD(TIMESTAMP WITH TIME ZONE) values stored with a specified ending element value consume 20 and 24 bytes of storage, respectively, for their ending element.
Procedure
Perform the following procedure to complete the Row Size Calculation form for each table.
- Identify each column in the table and enter its name in the Column Name column.
- Identify the data type for each column and enter it in the Type column next to the column name.
IF the type is … THEN … predefined use the sizing factors on the right-hand side of the form to determine the size of values stored for each data type. BLOB, CLOB, or XML enter 39 bytes for the value of its OID. BLOB, CLOB, and XML values are stored outside of the row in their own subtables, which also must be accounted for in your capacity planning.
a distinct, structured, or ARRAY UDT use the back of the form to tally its size. When you have identified and tallied all UDT columns in the table, copy their sum into the UDTs item in the sizing factors column on the right-hand side of the form.
- Determine the maximum value of the specified data type for each column in the table and enter the value in the Max column.
- Compute the average size of the specified data type for each column in the table and enter the value in the Avg column.
- Enter the total physical row size, rounded up to an even number of bytes, as Physical Size.