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 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). 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 4 + (17 + 4 a + 4 b + c + d) 4 4 (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-11)

d: Length of operation area for set functions (see Table 16-11)

e: 32-bit mode: MAX (4 N 24, 16408)

64-bit mode: MAX (8 N 40, 32808)

N: Value of the PDAGGR operand in the client environment definition

Table 16-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 function1
INTEGER	4	6
SMALLINT	2	42
DECIMAL(p,s)	(p + 1) 2	(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 n	2 n + 2
NVARCHAR(n)	2 n + 2	2 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

¹ If the set function is COUNT, the length of the operation area is always 6 regardless of the data type.

² If the set function is AVG or SUM, the length of the operation area is 6.

³ If the set function is AVG or SUM, the length of the operation area is 18.

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

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 1024 + 6 1024 b + c) i = 1 64-bit mode a (13 1024 + 7 1024 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, 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
1500 or less	Batch hash join		0.5
1500 (packet split count 3) or less	Packet split Hash join	1-level packet split	1
1500 (packet split count 3)2 or less		2-level packet split	2
Greater than 1500 (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 1012	4096
1013 to 2036	8192
2037 to 4084	16384
4085 to 16360	32768
16361 to 32720	(maximum length of hash table row + 48) 2048 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.

The following formula is used to determine the size of the memory required for foreign table retrieval (when the HiRDB External Data Access facility is used). You can check the SQL statements that retrieve from foreign tables with the access path display facility (pdvwopt command). When an SQL statement is executed on a foreign table, the memory requirement is 2,147,483,647 bytes.

Formula

a + 16 + 16b + c This is calculated when there are embedded variables or ? parameters in the SQL statement used for foreign table retrieval. + 16 + 16e + d (bytes)

a: Length of the SQL statement retrieving from the foreign table

b: Number of columns in the foreign table to be retrieved

c: If the foreign server's information definition contains the pd_hb_ary_fec_num operand:

• When the pd_hb_ary_fec_num operand is specified
  Total column length of the foreign table (see Table 16-5) value of the pd_hb_ary_fec_num operand
  
• When the pd_hb_ary_fec_num operand is not specified
  MAX{total column length of the foreign table (see Table 16-5), value of the pd_sql_send_buff_size operand 2048}
  

If the foreign server's information definition does not contain the pd_hb_ary_fec_num operand:

Total column length of the foreign table (see Table 16-5)

d: Total array length of embedded variables or ? parameters in the SQL statement that performs the foreign table retrieval (see Table 16-5)

e: Number of embedded variables or ? parameters in the SQL statement that performs the foreign table retrieval

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 2 (bytes)

a: Maximum page length in the RDAREA where the relevant table or index is stored

However, LOB RDAREAs are excluded.

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) (number of offset rows + number of rows specified in the LIMIT clause)} 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 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 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 17-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

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 500 + (20 + 6) 800 + 16 (bytes)

a: Row length. To determine the row length, use the following formula:

m (Ai) + 4 (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 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.

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 m) + a 64-bit mode (24 + 24 m) + a (bytes)

a: Total data length of ? parameters or embedded variables

m

a = (a_i)

i=1

m: Number of ? parameters or embedded variables in the SQL statement

a_i: Data length of the i^th ? parameter or the embedded variable

For details about the data length, see Table 16-5.

(b) When the PREPARE statement of the definition SQL is executed

Formula

SQL statement length + 20 (bytes)