15.2.6 Formulas for size of memory required during SQL execution
(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 front-end server 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 area on the basis of the value of the PDAGGR operand in the client environment definition. The size of the memory can be obtained from the following formula (in bytes). The size of the memory required during execution of rapid grouping facility should be calculated for the server machine defining the back-end server only.
- Formula
e + d 4 x 4 + (17 + 4 x a + 4 x b + c + d) 4 x 4 x (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 15-11)
- d: Length of operation area for set functions (see Table 15-11)
- e: 32-bit mode: MAX (4 x N x 24, 16,408)
- 64-bit mode: MAX (8 x N x 40, 32,808)
- N: Value of the PDAGGR operand in the client environment definition
Table 15-11 Length of column subject to grouping and length of operation area for set functions
Column's data type | Column length | Length of operation area for set function#1 |
---|
INTEGER | 4 | 6 |
SMALLINT | 2 | 4#2 |
DECIMAL(p,s) | (p + 1) 2![[Figure]](figure/zueng010.gif) | (p + 7) 2 #3 |
FLOAT | 8 | 10 |
SMALLFLT | 4 | 6 |
INTERVAL YEAR TO DAY | 5 | 8 |
INTERVAL HOUR TO SECOND | 4 | 6 |
CHAR(n) | n | n + 3 |
VARCHAR(n) | n + 2 | n + 5 |
NCHAR(n) | 2 x n | 2 x n + 2 |
NVARCHAR(n) | 2 x n + 2 | 2 x n + 4 |
MCHAR(n) | n | n + 3 |
MVARCHAR(n) | n + 2 | n + 5 |
DATE | 4 | 6 |
TIME | 3 | 6 |
BLOB(n) | -- |
BINARY(n) | n + 2 | n + 5 |
- Legend:
- --: 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 set function operation region is the following value:
- If the set function value type is DECIMAL and precision is 29 digits: 18
- If the set function value type is DECIMAL and precision is 38 digits: 23
- For details about the data type rules of set functions, see Set functions in the manual HiRDB Version 9 SQL Reference.
(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: 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
(13 x 1,024 + 6 x 1,024 x b + c) i=1
- 64-bit mode
a
(13 x 1,024 + 7 x 1,024 x b + c) (bytes) i=1 |
- 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 HiRDB Version 9 UAP Development Guide.
- b: Obtain the hash join processing to be applied on the basis of the number of hash table rows, then determine the this value from the following table:
Guidelines for the number of hash table rows | Hash join processing to be applied | Value of b |
---|
1,500 or less | Batch hash join | 0.5 |
1,500 x (packet split count 3) or less | Packet split Hash join | 1-level packet split | 1 |
1,500 x (packet split count 3)2 or less | 2-level packet split | 2 |
Greater than 1,500 x (packet split count 3)2 | 3-level packet split | 3 |
- 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 {
(size of hash table
2)
page length of hash table
, 64}
- Hash table size: Value of the pd_hash_table_size 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 hash table corresponding to c (maximum length of hash table row) from the following table:
Maximum length of hash table row | Page length of hash table (bytes) |
---|
0 to 1,012 | 4,096 |
1,013 to 2,036 | 8,192 |
2,037 to 4,084 | 16,384 |
4,085 to 16,360 | 32,768 |
16,361 to 32,720 | (maximum length of hash table row + 48) 2,048 x 2,048 |
- c: Maximum length of hash table row
- For details about the length of a hash table row, see the HiRDB Version 9 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: 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 Chapter 18. Determining Work Table File Size because a work table is created.
Formula
{100 + (a + 2) x (number of offset rows + number of rows specified in the LIMIT clause)} x b (bytes) |
- a: Row length
- The row length cannot exceed 32,720 bytes. The row length is calculated with the following formula:
m
(Ai) + 2 x m + 4 + c (bytes) i=1 |
- 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 is 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 16-1 List of data lengths, and determine the length beginning by assigning the defined length to d.
- The data length is set to 12 bytes for a column whose data type is BLOB, character string whose defined length is 256 bytes or greater (including national and mixed character strings), or BINARY that does not belong to any of the following:
- 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 maintenance areas for the first n records
- The number of maintenance 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 a 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 a search condition is executed, use the following formula:
- Formula
a x 500 + (20 + 6) x 800 + 16 (bytes) |
- a: Row length. To determine the row length, use the following formula:
m
(Ai) + 4 x (m + 2) + 12 + 4 + 8 (bytes) i=1 |
- 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 16-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 the join condition (join column)
- Column specified in the selection formula with the DISTINCT clause specified
- Column specified in the selection formula in a subquery of a quantified predicate
- Column specified in the selection formula in a subquery of an IN predicate
- Selection formula in the subquery that is the target of Set Operation due to UNION [ALL] or EXCEPT [ALL]
- Column specified in the 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 x m) + a (bytes) 64-bit mode (16 + 24 x m) + a (bytes) |
- a: Total data length of ? parameters or embedded variables
- m
- a =
(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
- For details about the data length, see Table 15-5.
(b) When the PREPARE statement of the definition SQL is executed
- Formula
SQL statement length + 20 (bytes) |
(8) Determining the size of memory required for defining substructure indexes or for updating tables in which a substructure index is defined
(a) If a substructure index is defined
Use the following formula to calculate the process private area used when a substructure index is defined with the CREATE INDEX statement of definition SQL.
- Formula
(index key length# x 100 + 64) (bytes) |
- #
- The maximum definition length of the substructure index defined in the table.
(b) If a table in which a substructure index is defined is updated
Use the following formula to calculate the amount of process private area used for updating a table in which a substructure index has been defined with the INSERT, UPDATE or DELETE statement of data manipulation SQL.
- Formula
(index key length#1 x 100 + 64 + 128) + (index key length + 128)#2 (bytes) |
- #1
- The maximum definition length of the substructure index defined in the table.
- #2
- The number of substructure indexes that specify USING UNIQUE TAG.
(9) Determining the size of the memory required to execute data manipulation SQL statements on compressed columns
If SQL statement execution, data storage processing, or extraction processing involves compressed columns, HiRDB allocates a process private area whose memory size is as shown below.
- Formula
MIN(split compression size, definition length of compressed column)# x C + L (bytes) |
- C: If any of the following conditions is true, 2; if not, 1:
- The SUBSTR function is used.
- The POSITION function is used.
- Backward deletion/updating of data is performed.
- L: Page length of the RDAREA containing the compressed table to be processed by the SQL statement
- If multiple RDAREAs are processed, use the maximum page length.
- #
- Use the maximum value for all the compressed columns subject to SQL statement processing.