5.8.2 Determining the number of pages for storing each type of row

(1) Determining the number of pages for base rows (variable BP(i))

Use the formula shown below to determine the number of pages for base rows (variable BP(i)). The part inside the parentheses in the denominator of variable BP(i) indicates the number of rows stored per page, and is a value in the range from 1 to 255.

Formula

Explanation of variables

• row_num

  Number of rows to be stored in the table (rows)

• page_size

  Page size in the data DB area (bytes)

• pctfree

  Percentage of unused area specified in the PCTFREE operand of the CREATE TABLE statement (%)

  If the percentage of unused area is not specified, assume 30% for the calculation.

  To obtain the number of basic row pages of data to be added to a column store table by using the INSERT or UPDATE statement, assume 0% for the calculation.

  Determine the value of pctfree such that it satisfies the following formula.

  Formula (pctfree value)

• CTRLSIZE

  If the target table is a row store table, specify 56.

  To obtain the number of basic row pages of data to be added to a column store table by using the INSERT or UPDATE statement, specify 80.

Use the formulas shown below to determine the row length (ROWSZ). The formula depends on whether the table is a non-FIX table for which the BRANCH ALL table option is not specified, is a non-FIX tables for which the BRANCH ALL table option is specified, or is a FIX table.

To obtain the number of basic row pages of data to be added to a column store table by using the INSERT or UPDATE statement, use the formula for a non-FIX table for which the BRANCH ALL table option is not specified.

Formula for a non-FIX table for which the BRANCH ALL table option is not specified

Formula for a non-FIX table for which the BRANCH ALL table option is specified

Formula for a FIX table

Explanation of variables

• col_num

  Total number of columns in the table (columns)

• col_size(i)

  Data length in each column (bytes)

  Determine the data length for each column based on the following table. Then, calculate the total for all columns.

  Table 5‒15: Data length of each data type

  No.	Classification	Data type		Data length (bytes)
  1	Numeric data	INTEGER		8
  2		SMALLINT		4
  3		DECIMAL(m, n)#	1 ≤ m ≤ 4	2
  4			5 ≤ m ≤ 8	4
  5			9 ≤ m ≤ 16	8
  6			17 ≤ m ≤ 38	16
  7		DOUBLE PRECISION		8
  8	Character string data	CHARACTER(n)		n
  9		VARCHAR(n)		var_col_size
  10	Datetime data	DATE		4
  11		TIME(p)		3 + ↑p ÷ 2↑
  12		TIMESTAMP(p)		7 + ↑p ÷ 2↑
  13	Binary data	BINARY(n)		n
  14		VARBINARY(n)		var_col_size

  Legend:

  m, n: Positive integers

  p: 0, 3, 6, 9 or 12

  #

  Indicates a fixed-point number that has a total of m digits, with n digits following the decimal point. If m is omitted, 38 is assumed.

var_col_size: Data length of VARCHAR and VARBINARY columns

Determine the data length based on the following table.

Table 5‒16: Data length of VARCHAR and VARBINARY columns

No.	Table option Whether BRANCH ALL is specified	Column definition BRANCH specification	Definition length n (in bytes)	Data length (in bytes)
1	Yes	--	--	9
2	No	YES	--	11
3		NO	1 ≤ n ≤ 255	d + 2
4			256 ≤ n ≤ 32,000	d + 3
5		AUTO or not specified	1 ≤ n ≤ 255	d + 2
6			256 ≤ n ≤ 32,000	11

Legend:

n: Positive integer

d: Actual data length

--: Not applicable

To Page Top

(2) Determining the number of pages for branch rows (variable VP(i))

Use the following formula to determine the number of pages for branch rows (variable VP(i)).

Formula

Explanation of variables

• row_num

  Number of rows to be stored in the table (rows)

• var_num

  Number of columns managed as branch rows (columns)

  Determine the number of the following types of columns:

  If the BRANCH ALL table option is specified

  • Number of VARCHAR and VARBINARY columns

  If the BRANCH ALL table option is not specified

  Total number of VARCHAR and VARBINARY columns that satisfy any of the following conditions:

  • Column for which YES is specified for the BRANCH column definition

  • Column whose defined length is 256 bytes or greater and for which AUTO is specified for the BRANCH column definition

  • Column whose defined length is 256 bytes or greater and for which the BRANCH column definition is omitted

• page_size

  Page size in the data DB area (bytes)

• pctfree

  Percentage of unused area specified in the PCTFREE operand of the CREATE TABLE statement (%)

  If the percentage of unused area is not specified, assume 30% for the calculation.

  To obtain the number of branch row pages of data to be added to a column store table by using the INSERT or UPDATE statement, assume 0% for the calculation.

  For pctfree, use the value that satisfies the formula in pctfree in (1) Determining the number of pages for base rows (variable BP(i)).

• var_size

  Average data length of columns in the branch rows (bytes)

  Determine the length based on the following table.

  Table 5‒17: Data length of columns that become branch rows

  Classification	Data type	Data length (bytes)
  Character string data	VARCHAR	d
  Binary data	VARBINARY	d

  Legend:

  d: Actual data length

• CTRLSIZE

  If the target table is a row store table, specify 56.

  To obtain the number of branch row pages of data to be added to a column store table by using the INSERT or UPDATE statement, specify 80.

To Page Top