The procedure used to calculate the number of pages required to store a table depends on whether FIX is specified for the table in CREATE TABLE. The procedures are explained in (1) and (2) as follows. The variables used in the formulas are explained in (3), and examples of calculating the number of pages needed to store a table are presented in (7). Estimating the RDAREA size when the rebalancing facility is used is explained in (6).
If a table is row-partitioned, the number of pages for the table in each storage RDAREA must be obtained.
When FIX is not specified, the following formula is used to calculate the number of pages needed to store a table.
|
![]() |
|
Use the following formula to obtain the value of P. The denominator enclosed in parentheses indicates the number of rows stored per page; its minimum and maximum values are 1 and 255, respectively.
![]() |
Use the following formula to obtain the value of each PSi and then obtain their sum, where n indicates the number of columns to which Table 16-2 Data lengths for the variable-length character string type (except abstract data type and repetition columns) is applicable.
PSi = a x![]() ![]() |
When FIX is specified, the following formula is used to calculate the number of pages needed to store a table.
![]() |
Use the following formula to obtain the value of Q, in which the denominator enclosed in parentheses indicates the number of rows stored per page; its minimum and maximum values are 1 and 255, respectively.
![]() |
SPN1 = f ![]() ![]() ![]() i=1 |
SPN2 = SPN2A + SPN2B + SPN2C![]() Calculate for the BINARY columns defined as branching. SPN2A = k { ![]() ![]() ![]() i=1 ![]() Li > (b - 2,853) ![]() SPN2B = k ![]() ![]() ![]() ![]() i=1 Li ![]() ![]() SPN2C = ![]() ![]() ![]() |
Table 16-1 List of data lengths
Classification | Data type | Data length (bytes) | |||
---|---|---|---|---|---|
Numeric data | INTEGER | 4 | |||
SMALLINT | 2 | ||||
LARGE DECIMAL(m,n)#1 | ![]() ![]() | ||||
FLOAT or DOUBLE PRECISION | 8 | ||||
SMALLFLT or REAL | 4 | ||||
Character data | CHARACTER(n) | n#3 | |||
VARCHAR(n) (variable-length character string) | d![]() | Element of repetition column | d + 2 | ||
Other | d + 1 | ||||
d![]() | 6 | ||||
VARCHAR (n) with no-split option specified | n![]() | Attribute of abstract data type | d + 3 | ||
Element of repetition column | d + 2 | ||||
Other | d + 1 | ||||
n![]() | Branching#5 | 6 | |||
Not branching#5 | Attribute of abstract data type | d + 3 | |||
Element of repetition column | d + 2 | ||||
Other | d + 3 | ||||
National character data | NCHAR(n) or NATIONAL CHARACTER(n) | 2 x n#4 | |||
NVARCHAR(n) | d![]() | Element of repetition column | 2 x d + 2 | ||
Other | 2 x d + 1 | ||||
d![]() | 6 | ||||
NVARCHAR(n) with no-split option specified | n![]() | Attribute of abstract data type | 2 x d + 3 | ||
Element of repetition column | 2 x d + 2 | ||||
Other | 2 x d + 1 | ||||
n![]() | Branching#5 | 6 | |||
Not branching#5 | Attribute of abstract data type | 2 x d + 3 | |||
Element of repetition column | 2 x d + 2 | ||||
Other | 2 x d + 3 | ||||
Mixed character string data | MCHAR(n) | n#3 | |||
MVARCHAR(n) | d![]() | Element of repetition column | d + 2 | ||
Other | d + 1 | ||||
d![]() | 6 | ||||
MVARCHAR(n) with no-split option specified | n![]() | Attribute of abstract data type | d + 3 | ||
Element of repetition column | d + 2 | ||||
Other | d + 1 | ||||
n![]() | Branching#5 | 6 | |||
Not branching#5 | Attribute of abstract data type | d + 3 | |||
Element of repetition column | d + 2 | ||||
Other | d + 3 | ||||
Date data | DATE | 4 | |||
Time data | TIME | 3 | |||
Date interval data | INTERVAL YEAR TO DAY | 5 | |||
Time interval data | INTERVAL HOUR TO SECOND | 4 | |||
Timestamp data | TIMESTAMP(n) | 7 + (n![]() | |||
Large object data | BLOB | 9 | |||
Binary data | BINARY(n) | n![]() | d + 3 | ||
n![]() | Branching#5 | 15 | |||
Not branching#5 | d + 3 | ||||
Binary data | BINARY(n) | n![]() | d + 3 | ||
n![]() | Branching#5 | 15 | |||
Not branching#5 | d + 3 | ||||
BINARY(n) with compression specified | Branching#5 | 15 | |||
Not branching#5 | ![]() |
BL > page length - 50 f BL (bytes) = ![]() i=1 |
Table 16-2 Data lengths for the variable-length character string type (except abstract data type and repetition columns)
Data type | Data length (bytes) | |
---|---|---|
VARCHAR (n) | d![]() | d + 2 |
No-split option specified | 0 | |
NVARCHAR (n) | d![]() | 2 x d + 2 |
No-split option specified | 0 | |
MVARCHAR (n) | d![]() | d + 2 |
No-split option specified | 0 |
Use the following formula to obtain data length di of an abstract data type column.
h di = ![]() k=1 |
m ADTk= ![]() i=1 |
For the data lengths for each attribute, see Table 16-1 List of data lengths. If the data type satisfies the condition shown in Table 16-3 Data lengths for the variable-length character string type (abstract data type), calculate the data according to Table 16-3.
Assign the value of the corresponding attej to the following formula and add branch row storage pages ADTLS to P:
h ADTLS= ![]() ![]() ![]() I=1 |
When attributes are defined as an abstract data type, use the following formula to obtain their data length:
h attj(bytes)= ![]() k=1 |
Table 16-3 Data lengths for the variable-length character string type (abstract data type)
Data type | Condition | Data length attj (bytes) | Data length of branch section attej (bytes) |
---|---|---|---|
VARCHAR (n) | d![]() | 8 | d + 2 |
No-split option specified | d + 3 | 0 | |
NVARCHAR (n) | d![]() | 8 | 2 x d + 2 |
No-split option specified | 2 x d + 3 | 0 | |
MVARCHAR (n) | d![]() | 8 | d + 2 |
No-split option specified | d + 3 | 0 |
Use the following formula to obtain the data length of a repetition column:
di = 4 + (eli + 1) x eni |
Table 16-4 Data lengths for the variable-length character string type (repetition columns)
Data type | Condition | Data length eli (bytes) | Data length of branch section esj (bytes) |
---|---|---|---|
VARCHAR (n) | d![]() | 5 | d + 2 |
No-split option specified | d + 2 | 0 | |
NVARCHAR (n) | d![]() | 5 | 2 x d + 2 |
No-split option specified | 2 x d + 3 | 0 | |
MVARCHAR (n) | d![]() | 5 | d + 2 |
No-split option specified | d + 2 | 0 |
If a repetition column with the variable-length character string type satisfies the value of eli shown in Table 16-4, add the value obtained from the following formula to P:
m![]() ![]() ![]() ![]() i=1 |
If there are partitioning tables that use any one of the HASHA through HASHF hash functions, the data is divided into 1,024 hash element values, each of which is stored in a separate segment.
An average of (1,024 number of partitions) hash elements of data is stored in each partitioned RDAREA. Therefore, at a minimum, enough segments must be allocated to each RDAREA to store the number of elements.
When the rebalancing facility is used, the RDAREA size can be estimated as follows:
Obtain the number of table storage pages for the following STOCK table:
PCODE | PNAME | STANDARD | PRICE | QUANTITY | COST |
---|---|---|---|---|---|
20180 | CLEANER | C20 | 20000 | 26 | 15000 |
20190 | CLEANER | C77 | 28000 | 105 | 23000 |
20130 | REFRIGERATOR | P10 | 30000 | 70 | 25000 |
20220 | TV | K18 | 35000 | 12 | 30000 |
20200 | CLEANER | C89 | 35000 | 30 | 30000 |
20140 | REFRIGERATOR | P23 | 35000 | 60 | 30000 |
20280 | AMPLIFIER | L10 | 38000 | 200 | 33000 |
20150 | REFRIGERATOR | P32 | 48000 | 50 | 43000 |
20290 | AMPLIFIER | L50 | 49800 | 260 | 45000 |
20230 | TV | K20 | 50000 | 15 | 45000 |
20160 | REFRIGERATOR | P35 | 55800 | 120 | 50000 |
Column | Data type |
---|---|
PCODE | CHARACTER(5) |
PNAME | CHARACTER(4) |
STANDARD | CHARACTER(3) |
PRICE | INTEGER |
QUANTITY | INTEGER |
COST | INTEGER |