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).
e + ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() (bytes) |
Table 16-4 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) | ![]() ![]() ![]() | ![]() ![]() ![]() |
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 ![]() | 2 ![]() |
NVARCHAR(n) | 2 ![]() | 2 ![]() |
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 |
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):
a + 128 (bytes) |
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:
![]() ![]() ![]() ![]() i = 1
![]() ![]() ![]() ![]() i = 1 (bytes) |
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 hash join | 1-level packet split | 1 |
1500 ![]() ![]() | 2-level packet split | 2 | |
Greater than 1500 ![]() ![]() | 3-level packet split | 3 |
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 | ![]() ![]() ![]() ![]() |
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.
a![]() |
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) ![]() ![]() (bytes) |
m![]() ![]() i=1 (bytes) |
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 search condition is executed, use the following formula:
a![]() ![]() |
m![]() ![]() i=1 (bytes) |
When the facility for output of extended SQL error information is used, a process private area is allocated in the following cases:
32-bit mode (16 + 16 ![]() 64-bit mode (24 + 24 ![]() (bytes) |
Table 16-5 Data length of embedded variables and ? parameters
Data type | Column length (without indicator variable) | Column length (with indicator variable, embedded variable, and ? parameter) |
---|---|---|
INTEGER | 4 | 6 |
SMALLINT | 2 | 4 |
DECIMAL(p,s) | ![]() ![]() ![]() | ![]() ![]() ![]() |
FLOAT | 8 | 10 |
SMALLFLT | 4 | 6 |
INTERVAL YEAR TO DAY | 5 | 7 |
INTERVAL HOUR TO SECOND | 4 | 6 |
CHAR(n) | n | n + 2 |
VARCHAR(n) | n + 2 | n + 4 |
NCHAR(n) | 2 ![]() | 2 ![]() |
NVARCHAR(n) | 2 ![]() | 2 ![]() |
MCHAR(n) | n | n + 4 |
MVARCHAR(n) | n + 2 | n + 4 |
DATE | 4 | 6 |
TIME | 3 | 5 |
BLOB(n) | n + 4 | n + 8 |
TIMESTAMP(p) | 7 + (p![]() | 9 + (p![]() |
BINARY(n) | n + 4 | n + 8 |
SQL statement length + 20 (bytes) |