How to calculate the size of the ROW
Sometimes we need to calculate the size of a row of records, and give a method of how to calculate the ROW size.
Step 1: Calculate the size of the entire Block Header
The size of the data block heads is calculated by the following formula:
SpaceAfter headers (hsize) = db_block_size - KCBH - UB4 - KTBBH - ((Initrans - 1) * Ktbit) - KDBH
Some of these parameters are as follows:
DB_BLOCK_SIZE: The database's Block size can be obtained in the V $ Parameter view.
KCBH, UB4, KTBBH, KTBIT, KDBH are constants that can be obtained from the V $ TYPE_SIZE view.
INITRANS is the number of initialization transactions assigned to the table, which can be obtained from the INI_TRANS field in the USER_TABLES table.
Step 2: Calculate the data space available for each data block.
The space preserved for each data block is specified by the PCTFree parameter, so the calculation formula is as follows:
Available Data Space (Availspace) = CEIL (Hsize * (1 - PctFree / 100))
- KDBT
Some of these parameters are as follows:
CEIL is a minimum integer greater than or equal to N.
PCTFREE is a space that reserves the UPDATE operation in the table, which can be obtained from the PCT_FREE field in the user_tables table.
KDBT is constant, the defined size can be obtained from the V $ TYPE_SIZE view. If you can't find the definition size of the KDBT, use the size defined by UB4 instead.
Step 3: Calculate the size of the space used per line:
Calculating the space size used by each row is done multi-step computing.
First calculate the size of the column, including the length of bytes:
Column size including byte length = column size (1, IF Column Size <250, ELSE 3)
For the size of the column, you can use the experience to determine its size, or you can use the statement to calculate the size of each column:
Select AVG (VSIZE (ColName) from Table_name;
Then, the size of the line is calculated:
Rowsize = row header (3 * UB1) SUM of Column Sizes Including Length Bytes
Finally, calculate the space size used for each row:
Space buy per row = max (UB1 * 3 UB4 SB2, ROWSIZE) SB2
UB1, UB4, SB2 are all constants, and the defined size can be obtained from the V $ TYPE_SIZE view.
When the space occupied by each row exceeds the size of the space available, it is still less than the space size of the Update operation (for example, PCTFREE = 0), and each line record will still be stored in them. BLOCK block.
When the space of each row exceeds the space available for each data block, and there is no space that is reserved to the Update operation, this line record will be linked to another Block or more, so this time The stored load will be relatively high.
Step 4: Calculate the maximum number of records in a block.
The following formula can be used to calculate the number of records that can be accommodated in a data block:
Number of rows in block = floor (availspace / rowspace) Floor is a maximum integer that is less than N.
This process is just a substantially estimated size of a table, not accurate to calculate. After roughly estimating a table size, we can use this information to determine how much Initial is used when creating a table. When the table is created, the demand for space is often greater than that of our estimated value.
