16.1.6 Formulas for size of memory required during SQL execution

Organization of this subsection
(1) Procedure for obtaining the size of the memory required during execution of rapid grouping facility
(2) Procedure for obtaining the size of the memory required when data suppression by column is specified
(3) Procedure for obtaining the size of the memory required during hash join and subquery hash execution
(4) Procedure for obtaining the size of the memory required when the snapshot method is used
(5) Determining the size of the memory required to retrieve the first n records
(6) Determining the size of the memory required for executing SQL statements specifying an index-type plug-in function as search condition
(7) Determining the size of the memory required to use the facility for output of extended SQL error information

(1) Procedure for obtaining the size of the memory required during execution of rapid grouping facility

If PDSQLOPTLVL is specified in the client environment definition, pd_optimize_level is specified in the system common definition, or this operand is omitted, executing an SQL statement that satisfies the applicable conditions will activate the rapid grouping facility. In such a case, HiRDB allocates process private memory on the basis of the value of PDAGGR in the client environment definition. The size of the memory can be obtained from the following formula (in bytes).

Formula:
e + [Figure]d[Figure] 4[Figure][Figure] 4 + [Figure] (17 + 4 [Figure] a + 4 [Figure] b + c + d) [Figure] 4[Figure][Figure] 4 [Figure] (N + 1)
(bytes)
a: Number of columns subject to grouping
b: Number of operations by set functions
Each of COUNT, SUM, MAX, and MIN is counted as 1.
Each of AVG (COUNT) and AVG (SUM) is counted as 2.
c: Length of rows subject to grouping (see Table 16-4)
d: Length of operation area for set functions (see Table 16-4)
e: 32-bit mode: MAX (4 [Figure] N[Figure] 24, 16408)
64-bit mode: MAX (8 [Figure] N[Figure] 40, 32808)
N: Value of PDAGGR operand in the client environment definition

Table 16-4 Length of column subject to grouping and length of operation area for set functions

Column's data typeColumn lengthLength of operation area for set function1
INTEGER46
SMALLINT242
DECIMAL(p,s)[Figure](p + 1) [Figure] 2[Figure][Figure](p + 7) [Figure] 2[Figure] 3
FLOAT810
SMALLFLT46
INTERVAL YEAR TO DAY58
INTERVAL HOUR TO SECOND46
CHAR(n)nn + 3
VARCHAR(n)n + 2n + 5
NCHAR(n)2 [Figure] n2 [Figure] n + 2
NVARCHAR(n)2 [Figure] n + 22 [Figure] n + 4
MCHAR(n)nn + 3
MVARCHAR(n)n + 2n + 5
DATE46
TIME36
BLOB(n)[Figure]
BINARY(n)n + 2n + 5
Legend:
[Figure]: Not applicable
1 If the set function is COUNT, the length of the operation area is always 6, regardless of the data type.
2 If the set function is AVG or SUM, the length of the operation area is 6.
3 If the set function is AVG or SUM, the length of the operation area is 18.

(2) Procedure for obtaining the size of the memory required when data suppression by column is specified

The following formula can be used to obtain the size of the memory (in bytes) required to access a table for which data suppression by column is specified (table for which SUPPRESS is specified in the column definition of CREATE TABLE):

Formula:
a + 128 (bytes)
a: Sum of the lengths of columns in the table for which data suppression by column is specified

(3) Procedure for obtaining the size of the memory required during hash join and subquery hash execution

If you specify the PDADDITIONALOPTLVL operand in the client environment definition or the pd_additional_optimize_level operand in the HiRDB system definition, the SQL extension optimizing option becomes available. If you specify an application of "hash join, subquery hash execution (APPLY_HASH_JOIN)" with this SQL extension optimizing option, the system allocates the following size of process private area when a table join or subquery SQL statement is executed:

Formula
32-bit mode
a
[Figure] (13 [Figure] 1024 + 6 [Figure] 1024 [Figure] b + c)
i = 1
64-bit mode
a
[Figure] (13 [Figure] 1024 + 7 [Figure] 1024 [Figure] b + c)
i = 1
(bytes)
a: Maximum number of hash joins in the SELECT statement
For details about the maximum number of hash joins in the SELECT statement, see the manual HiRDB Version 8 UAP Development Guide.
b: Obtain the hash join processing to be applied on the basis of the number of hash table rows, and then determine this value from the following table:
Guidelines for the number
of hash table rows
Hash join processing
to be applied
Value of b
1500 or lessBatch hash join0.5
1500 [Figure] (packet split count [Figure] 3) or lessPacket split
hash join
1-level packet split1
1500 [Figure] (packet split count [Figure] 3)2 or less2-level packet split2
Greater than 1500 [Figure] (packet split count [Figure] 3)23-level packet split3
Number of hash table rows: For join, it is the inner table count; for subquery, it is the subquery search count excluding the predicates that contain external reference rows in the search condition.
Packet split count: MIN {[Figure](size of hash table [Figure] 2) [Figure] page length of hash table[Figure], 64}
Hash table size: Value of the pd_hash_table_size operand specified in the HiRDB system definition or the value of the PDHASHTBLSIZE operand specified in the client environment definition.
Page length of hash table: Select the page length of the hash table corresponding to c (maximum length of hash table row) from the following table:
Maximum length of hash table rowPage length of hash table (bytes)
0 to 10124096
1013 to 20368192
2037 to 408416384
4085 to 1636032768
16361 to 32720[Figure](maximum length of hash table row + 48) [Figure] 2048[Figure][Figure] 2048
c: Maximum length of hash table row
For details about the length of a hash table row, see the manual HiRDB Version 8 UAP Development Guide.

(4) Procedure for obtaining the size of the memory required when the snapshot method is used

If the pd_pageaccess_mode operand is omitted, or if SNAPSHOT is specified, then the page access method for data retrieval uses the snapshot method when an SQL statement for which the snapshot method is applicable is executed. At this time, memory in the process private area is allocated automatically, as shown below, based on the page size of the table or index storage RDAREA.

Formula
a[Figure] 2 (bytes)
a: Maximum page length in the RDAREA where the relevant table or index is stored
However, LOB RDAREAs are excluded.

(5) Determining the size of the memory required to retrieve the first n records

If the function for retrieving n rows of search results from the top is used, you can retrieve n rows from the top of the search results (or from the location resulting from skipping as many rows from the top as specified by the user as an offset).

If the number of rows specified in the LIMIT clause is 1 or greater and the value of (number of offset rows + number of rows specified in the LIMIT clause) is 32,767 or less, as many rows are retained in memory as will fit in (number of offset rows + number of rows specified in the LIMIT clause). The size of the process private area to be allocated can be determined by the formula shown below. If the value of (number of offset rows + number of rows specified in the LIMIT clause) is 32,768 or greater, see 19. Determining Work Table File Size because a work table is created.

Formula

{100 + (a + 2) [Figure] (number of offset rows + number of rows specified in the LIMIT clause)} [Figure] b
(bytes)
a: Row length
The row length cannot exceed 32,720 bytes. The row length is calculated with the following formula:
m
[Figure](Ai) + 2 [Figure]m + 4 + c
i=1
(bytes)
m: Number of rows specified in the selection formula, GROUP BY clause, or ORDER BY clause
Add 1 if the FOR UPDATE clause is specified. However, if ROW is specified in the selection formula, this becomes the total number of rows in the table.
Ai: Data length of the ith column of the records stored in the first n records of the allocation area
For details about column data length, see Table 17-1 List of data lengths, and determine the length beginning by assigning the defined length to d.
However, for BLOB data, character data with a fixed length of at least 256 bytes (including National character data and mixed character string data), or BINARY data of columns without the following attributes, the value is 12:
  • Columns specified in a selection formula with the DISTINCT clause specified
  • A query specification selection formula using a concatenation operation based on UNION [ALL]
  • Columns specified in the ORDER BY clause
Also, if the FOR UPDATE clause is specified and 1 is added for m, use 12 bytes for Ai.
c: 8
However, in the following cases, use 0:
  • There is an exclusive lock in the EX mode on the retrieval table
  • WITHOUT LOCK is specified
  • The rapid grouping facility is specified
  • Multiple tables are combined
b: Number of allocated areas for the first n records
The number of allocated areas for the first n records is calculated with the following formula:
1 + number of UNION [ALL] clause specifications

(6) Determining the size of the memory required for executing SQL statements specifying an index-type plug-in function as search condition

To determine the size of memory that is allocated in the process private area when an SQL statement specifying an index-type plug-in function as search condition is executed, use the following formula:

Formula
a[Figure] 500 + (20 + 6) [Figure] 800 + 16 (bytes)
a: Row length. To determine the row length, use the following formula:
m
[Figure](Ai) + 4 [Figure] (m + 2) + 12 + 4 + 8
i=1
(bytes)
m: Number of columns specified in the selection formula, join condition, GROUP BY clause, or ORDER BY clause
If you specified the FOR UPDATE clause, add 1. If ROW is specified in the selection formula, the total number of rows in the table is assumed.
Ai: Length of column data i in the row to be retrieved
For details about column data length, see Table 17-1 List of data lengths, and determine the length beginning by assigning the defined length to d.
A length of 12 bytes is assumed for a column with BLOB data or character string data with a defined length of 256 bytes or greater (including national character data and mixed character string data) that is none of the following:
  • Column specified in a join condition (join column)
  • Column specified in a selection formula specifying the DISTINCT clause
  • Column specified in a selection formula in a subquery of a quantified predicate
  • Column specified in the selection formula in a subquery of IN predicate
  • Selection formula in a subquery that is the target of Set Operation due to UNION [ALL] or EXCEPT [ALL]
  • Column specified in an ORDER BY clause
If the FOR UPDATE clause is specified, Ai corresponding to 1 that was added to m is 12 bytes.

(7) Determining the size of the memory required to use the facility for output of extended SQL error information

When the facility for output of extended SQL error information is used, a process private area is allocated in the following cases:

(a) When the OPEN statement is executed
Formula
32-bit mode
(16 + 16 [Figure] m) + a
64-bit mode
(24 + 24 [Figure] m) + a
(bytes)
a: Total data length of ? parameters or embedded variables
m
a=[Figure](ai)
i=1
m: Number of ? parameters or embedded variables in the SQL statement
ai: Data length of the ith ? parameter or the embedded variable
Table 16-5 shows the data length of embedded variables and ? parameters.

Table 16-5 Data length of embedded variables and ? parameters

Data typeColumn length (without indicator variable)Column length (with indicator variable, embedded variable, and ? parameter)
INTEGER46
SMALLINT24
DECIMAL(p,s)[Figure](p + 1) [Figure] 2[Figure][Figure](p + 5) [Figure] 2[Figure]
FLOAT810
SMALLFLT46
INTERVAL YEAR TO DAY57
INTERVAL HOUR TO SECOND46
CHAR(n)nn + 2
VARCHAR(n)n + 2n + 4
NCHAR(n)2 [Figure] n2 [Figure] n + 2
NVARCHAR(n)2 [Figure] n + 22 [Figure] n + 4
MCHAR(n)nn + 4
MVARCHAR(n)n + 2n + 4
DATE46
TIME35
BLOB(n)n + 4n + 8
TIMESTAMP(p)7 + (p[Figure] 2)9 + (p[Figure] 2)
BINARY(n)n + 4n + 8
(b) When the PREPARE statement of the definition SQL is executed
Formula
SQL statement length + 20
(bytes)