6.3.4 Determining the memory requirement during normal operation

During normal operation following HADB server startup (when an HADB client connects to the HADB server and executes an SQL statement), the HADB server uses the types of memory listed below. Determine the requirement for each type of memory.

■ Shared memory

Process common memory (PROC_EXECSZ)
Real thread private memory (RTHD_EXECSZ)

■ Process memory

Heap memory (HEAP_EXECSZ)

The following subsections describe the formula for determining each of these memory requirements.

Organization of this subsection

(1) Determining the process common memory requirement (during normal operation)
(2) Determining the real thread private memory requirement (during normal operation)
(3) Determining the heap memory requirement (during normal operation)

(1) Determining the process common memory requirement (during normal operation)

Use the following formula to determine the process common memory requirement for normal operation (PROC_EXECSZ).

Formula (kilobytes)

[Figure]

#

Add this value when you use the multi-node function.

Explanation of variables

PROC_CNCTSZ

Process common memory used for connecting to the database

See (a) Determining the variable PROC_CNCTSZ.
PROC_TOTALSQLSSZ

Process common memory used for preprocessing SQL statements

See (b) Determining the variable PROC_TOTALSQLSSZ.
PROC_EXECSQLSZ

Process common memory used for executing SQL statements

See (c) Determining the variable PROC_EXECSQLSZ.
PROC_BUFWORK_CTL

Process common memory used for accessing work tables

See (d) Determining the variable PROC_BUFWORK_CTL.
PROC_DEFSQLSZ

Process common memory used for executing definition SQL statements

See (e) Determining the variable PROC_DEFSQLSZ.
PROC_IMPORTSZ

Process common memory used for executing the adbimport command

Determine the value as explained in (1) Determining the process common memory requirement (for executing the adbimport command) under 6.3.6 Determining the memory requirement for executing the adbimport command.
PROC_IDXRBLDSZ

Process common memory used for executing the adbidxrebuild command

Determine the value as explained in (1) Determining the process common memory requirement (for executing the adbidxrebuild command) under 6.3.7 Determining the memory requirement for executing the adbidxrebuild command.
PROC_GETCOSTSZ

Process common memory used for executing the adbgetcst command

Determine the value as explained in (1) Determining the process common memory requirement (for executing the adbgetcst command) under 6.3.8 Determining the memory requirement for executing the adbgetcst command.
PROC_DBSTATUSSZ

Process common memory used for executing the adbdbstatus command

Determine the value as explained in (1) Determining the process common memory requirement (for executing the adbdbstatus command) under 6.3.9 Determining the memory requirement for executing the adbdbstatus command.
PROC_MODASZ

Process common memory used for executing the adbmodarea command

See (1) Determining the process common memory requirement (for executing the adbmodarea command) in 6.3.12 Determining the memory requirement for executing the adbmodarea command.
PROC_EXPORTSZ

Process common memory used for executing the adbexport command

Determine the value as explained in 6.3.10 Determining the memory requirement for executing the adbexport command.
PROC_MERGCSZ

Process common memory used for executing the adbmergechunk command

Determine the value as explained in (1) Determining the process common memory requirement (for executing the adbmergechunk command) under 6.3.13 Determining the memory requirement for executing the adbmergechunk command.
PROC_UPDSZ

Process common memory used when a definition SQL statement or update SQL statement is executed for the first time after a connection to the database is established

Determine the value as explained in (f) Determining the variable PROC_UPDSZ.
PROC_CHGCCMSZ

Process common memory used during execution of the adbchgchunkcomment command

Determine the value as explained in 6.3.14 Determining the memory requirement for executing the adbchgchunkcomment command.
PROC_CHGCSTSZ

Process common memory used during execution of the adbchgchunkstatus command

Determine the value as explained in 6.3.15 Determining the memory requirement for executing the adbchgchunkstatus command.
PROC_UPDLISTSZ

Process common memory used for using the multi-node function

See (g) Determining the variable PROC_UPDLISTSZ.
PROC_ARCCKSZ

Process common memory used for executing the adbarchivechunk command

See (1) Determining the process common memory requirement (for executing the adbarchivechunk command) in 6.3.16 Determining the memory requirement for executing the adbarchivechunk command.
PROC_UNARCCKSZ

Process common memory used for executing the adbunarchivechunk command

See (1) Determining the process common memory requirement (for executing the adbunarchivechunk command) in 6.3.17 Determining the memory requirement for executing the adbunarchivechunk command.
PROC_REORGSZ

Process common memory used for executing the adbreorgsystemdata command

See (1) Determining the process common memory requirement (for executing the adbreorgsystemdata command) in 6.3.18 Determining the memory requirement for executing the adbreorgsystemdata command.
PROC_SYNDICTSZ

Process common memory used for executing the adbsyndict command

See (1) Determining the process common memory requirement (for executing the adbsyndict command) in 6.3.20 Determining the memory requirement for executing the adbsyndict command.
PROC_AUDTRAILSZ

Process common memory used during execution of the adbaudittrail command

See (1) Determining the process common memory requirement (for executing the adbaudittrail command) in 6.3.21 Determining the memory requirement for executing the adbaudittrail command.

(a) Determining the variable PROC_CNCTSZ

The variable PROC_CNCTSZ is required when connecting to databases. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variables

BUFCNCT: Connection buffer

Use the following formula to determine its value.

Formula (kilobytes)

BUFCNCT =
       ↑(56 + 8,216 × rthd_num) × max_users ÷ 1,024↑

rthd_num

Use the following formula to determine the value:

value-specified-for-adb_sys_rthd_num-operand-in-server-definition + 1

However, use 0 for the variable rthd_num if there is no adbbuff operand in the server definition for which the -v option or -k option is specified.

max_users: Value specified for the adb_sys_max_users operand in the server definition

SQLWQUE: SQL statement execution queue

Use the following formula to determine its value.

Formula (kilobytes)

SQLWQUE = ↑8 × max_users ÷ 1,024↑

max_users: Value specified for the adb_sys_max_users operand in the server definition

SQLACTB: SQL statement table access information

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

max_table_num: Maximum number of tables accessed by each SQL statement executed in the target connection

RTHDLOG

Log management area for processing real threads

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

rthd_num: Value specified for the adb_sys_rthd_num operand in the server definition

(b) Determining the variable PROC_TOTALSQLSSZ

The variable PROC_TOTALSQLSSZ is required when preprocessing SQL statements. Use the following formula to determine its value.

Formula (kilobytes)

PROC_TOTALSQLSSZ = MAXSQLWRK + TOTALSQLSCT + PREDICSZ + PROC_AUDINFSZ

Explanation of the variables

MAXSQLWRK

Work area used for a transaction during preprocessing of SQL statements

Use the following formula to determine its value.

Formula (kilobytes)

MAXSQLWRK = (max_sql(SQLPSZ) + 20) × max_users

max_sql()

Substitute the maximum value obtained when calculating SQLPSZ

max_users

Value specified for the adb_sys_max_users operand in the server definition

SQLPSZ

Work areas used when preprocessing SQL statements

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

tbln: Number of tables specified in the FROM clause
gbycoln: Number of columns specified in the GROUP BY clause
obysorn: Number of sort keys specified in the ORDER BY clause
updsetn: Number of SET clauses in the UPDATE statement
inscolnamn: Number of columns to be inserted by the INSERT statement
identn: Number of identifiers
drvt_num: Number of derived tables
max_query_spec: Maximum number of query specifications that can be specified in an SQL statement
vtbl: Number of viewed tables in an SQL statement
vquery_num: Number of query specifications specified in the view definition
vjotbl_num: Number of joined tables specified in the view definition
vtbl_num: Number of base tables specified in the view definition
jointbln: Number of joined tables
subqun: Number of subqueries
queryn: Number of query specifications
col_num: Number of selection expressions
pwl: Number of WITH clauses
drvtbln: Number of derived tables specified in the FROM clause
drvcolnum: Number of columns in derived tables
fmt_len: Format size of the CONVERT scalar function (bytes)
drvtblnum: Number of derived tables
viewcolnum: Number of columns in viewed tables
viewtblnum: Number of viewed tables
qurycolnum: Number of columns in query names
qurytblnum: Number of query names
charnum: Number of bytes in each SQL statement
vcol_num: Number of columns specified in the view definition
vwindow_num: Number of window functions specified in the view definition
vgroup_num: Number of GROUP BY clauses specified in the view definition
vsetf_num: Number of set functions specified in the view definition
gbyn: Number of GROUP BY clauses
qexp: Number of set operations
drvjotblnum: Number of joined tables specified in a derived table
drvquerynum: Number of queries specified in a derived table
drvwindownum: Number of window functions specified in a derived table
drvgroupnum: Number of GROUP BY clauses specified in a derived table
drvsetfnum: Number of set functions specified in a derived table
projn: Number of value expressions
max_drvtbln: Maximum number of tables that can be specified in the FROM clause
fjoin_drvquerynum: Number of queries specified in a derived table contained in FULL OUTER JOIN
fjoin_tbln: Number of table references in FULL OUTER JOIN
fjoin_tblcoln: Number of columns in table references in FULL OUTER JOIN
winfuncn: Number of window functions
query_expn: Number of query expressions
setfuncn: Number of set functions (+2 in the case of AVG)
drv_setopnum: Number of set operations specified in a derived table
archk_tbln: Number of archivable multi-chunk tables
csvrd_fieldnonum: Number of field data numbers specified in the ADB_CSVREAD function
predn: Number of predicates
qurytblfuncn: Number of table function derived tables specified for query names
tblfuncn: Number of table function derived tables
colspen: Number of column specifications
concat_n: Number of concatenation operations
gname_coln: Number of grouping column column names
inscoln: Number of inserted columns
prep_tbln: Number of pre-processing tables
withlistnum_recursion: Number of WITH list elements of recursive queries
withlistnum_recursion_query: Number of query specifications in recursive queries
vsetop_num: Number of set operations specified in the view definition
audrd_flpthn: Number of audit trail file path names specified in the ADB_AUDITREAD function
tblfunc_coln: Number of columns in table function derived tables
setfunc_distn: Number of DISTINCT set functions

TOTALSQLSCT

Total preprocessing results control area created by a transaction during preprocessing of SQL statements

When SQL statements are executed from the JDBC driver, this is the total preprocessing results control area for preprocessing of SQL statements created at the same time inside a single connection.

Use the following formula to determine its value.

Formula (kilobytes)

TOTALSQLSCT = sum_sql(SQLPSSZ + SQLSSZ + APATHVIEW) × max_users

max_users

Value specified for the adb_sys_max_users operand in the server definition

sum_sql ()

Substitute the maximum value among the calculated results for the variable SQLSSZ.

When SQL statements are executed from the JDBC driver, substitute the maximum value among the values determined for the SQL statements to be executed in a single connection.

SQLPSSZ

Preprocessing results control area acquired for preprocessing of SQL statements

Use the following formula to determine its value.

Formula (kilobytes)

SQLPSSZ = ↑184 ÷ 1,024↑

SQLSSZ

Preprocessing results control area used for preprocessing of SQL statements

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

#: For a table that stores cost information, replace this value with 32,776.
tblcoln: Number of columns in the table being processed
tblidxn: Number of indexes defined for the table being processed
tbln: Number of tables specified in the FROM clause
col_num: Number of selection expressions
insvaln: Number of insertion values
inscoln: Number of inserted columns
updcoln: Number of updated columns
colspen: Number of column specifications
gbycoln: Number of grouping columns specified in the GROUP BY clause
operan: Number of arithmetic operations
liken: Number of LIKE predicates
inn: Number of IN predicates
scafuncn: Number of scalar functions
setfuncn: Number of set functions (+2 in the case of AVG)
notn: Number of NOTs
orn: Number of ORs
param_num: Number of dynamic parameters
sregn: Total number of datetime information acquisition functions and user information acquisition functions
rown: Number of ROWs
jointbln: Number of joined tables
inscolnamn: Number of columns to be inserted by the INSERT statement
subqun: Number of subqueries
ord_num: Number of columns specified in the ORDER BY clause
queryn: Number of query specifications
logicn: Number of AND, OR, and NOT logical operations
predn: Number of predicates
base_num: Number of underlying tables specified in the view definition
sel_num: Number of selection expressions with query specification specified in the view definition
pwg: Number of labeled durations
scl: Number of columns (sum total of the number of column specifications and the number of column specifications after expansion with * specification)
sgc: Number of grouping columns
ssk: Number of sort keys specified in the ORDER BY clause
srw: Number of DECIMAL types and TIMESTAMP types contained in tables targeted by ROW
exp_num: Total number of value expressions
sum_view_object (): Sum total of the view object sizes of viewed tables specified in an SQL statement for the variable in parentheses (bytes)
view_size: View object size (value of the VIEW_OBJECT_SIZE column of the SQL_VIEWS dictionary table (base table)) (bytes)
qexp_coln: Number of columns in a query expression within a set operation
gname_coln: Number of grouping column column names
default_coln: Number of columns for which the DEFAULT clause is specified
act_num: Total number of THENs and ELSEs in a CASE expression
decode_revn: Number of return values for the DECODE scalar function
fmt_len: Format size of the CONVERT scalar function (bytes)
obysorn: Number of sort items specified in the ORDER BY clause
pw_when: Number of WHENs specified in a CASE expression
projn: Number of value expressions
fulljoin_num: Number of times FULL OUTER JOIN is specified in SQL statements
fj_join_num: Number of joined tables specified in FULL OUTER JOIN
gbyn: Number of GROUP BY clauses
qexp: Number of set operations
query_expn: Number of query expressions
winfuncn: Number of window functions
withlistnum: Number of elements in the WITH list
likeregn: Number of the LIKE_REGEX predicates
tblfuncn: Number of table function derived tables
multisetn: Number of multiset value expressions
rowvaluecon: Number of row value constructors
tablevaluecon: Number of table value constructors
rowvaluecon_coln: Number of columns of a row value constructor
gexp_coln: Number of group value expressions
fucall_paramn: Number of function call arguments
csvrd_n: Number of ADB_CSVREAD functions
csvrd_fieldnonum: Number of field data numbers specified in the ADB_CSVREAD function
multiset_projn: Number of multiset elements in a multiset value expression
archk_tbln: Number of archivable multi-chunk tables
archk_coln: Number of derived columns in an archivable multi-chunk table
drvtblnum: Number of derived tables
comp_dec_num: Number of operations that specify DECIMAL type data and INTEGER or SMALLINT type data in comparison operands
contains_n: Number of CONTAINS scalar functions
def_char_datesn: Number of predefined character-string representations in datetime data
drvcolnum: Number of columns in derived tables
tblfunc_coln: Number of columns in table function derived tables
regex_num: Number of LIKE_REGEX predicates specified
scalfunc_paramn: Number of scalar function arguments
setfuncn0: Number of set functions
winf_part_projn: Number of value expressions in window partition clauses
audrd_n: Number of ADB_AUDITREAD functions
ltdecode_revn: Number of return values for the LTDECODE scalar function
randcursornum: Number of RANDOMCURSOR scalar functions
randrownum: Number of RANDOMROW scalar functions

APATHVIEW

Access path information control area that is created for displaying an access path

Use the following formula to determine its value.

Formula (kilobytes)

APATHVIEW =
   ↑((9,728 + 1,024 × scl) × tbln + 2,200) ÷ 4,096↑ × 5

tbln

Number of tables specified in the FROM clause

scl

Number of columns (sum total of the number of column specifications and the number of column specifications after expansion with * specification)

PREDICSZ

Substitute the following value.

Value (kilobytes)

[Figure]

PROC_AUDINFSZ

Determine the value of the variable PROC_AUDINFSZ according to (r) Determining the variable AUDINF under (3) Determining the process common memory requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

(c) Determining the variable PROC_EXECSQLSZ

The variable PROC_EXECSQLSZ is required when executing SQL statements. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

#: Add this value when executing the PURGE CHUNK or TRUNCATE TABLE statement.

Explanation of variables

TRANSNAP

Transaction information snapshot

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

max_users

Value specified for the adb_sys_max_users operand in the server definition

RTHDUPDINF

Processing real thread update information

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the server definition

If the adb_sql_exe_max_rthd_num operand is specified in the client definition, assign that value (the value of the adb_sql_exe_max_rthd_num operand in the client definition). However, if the value of the adb_sql_exe_max_rthd_num operand in the client definition is greater than the value of the adb_sql_exe_max_rthd_num operand in the server definition, assign the value of the adb_sql_exe_max_rthd_num operand in the server definition.

For details, see the explanation of the adb_sql_exe_max_rthd_num operand in 7.2.2 Operands related to performance (set format).

DBUPDINF

DB area, table, index, and chunk update information

Use the following formula to determine this value. Note that the formula to be used differs depending on whether the multi-node function is used.

Formula (kilobytes)

[Figure]

archive_file_num

Specify the number of archive files for the archived chunk.

Use the adbdbstatus command to output the summary information of archived chunks, and then check the value of Archive_file_num for each chunk. For details about Archive_file_num, see the following section in the manual HADB Command Reference: List of items that are output in the summary information of archived chunks in Items that are output in the summary information of archived chunks in adbdbstatus (Analyze the Database Status).

(d) Determining the variable PROC_BUFWORK_CTL

The variable PROC_BUFWORK_CTL is required when a work table is used. Use the following formula to determine its value.

Formula (kilobytes)

PROC_BUFWORK_CTL = max_users + rthd_num

Explanation of variables

max_users: Value specified for the adb_sys_max_users operand in the server definition

rthd_num: Value specified for the adb_sys_rthd_num operand in the server definition

(e) Determining the variable PROC_DEFSQLSZ

The variable PROC_DEFSQLSZ is required when executing definition SQL statements. Use the following value.

The value to substitute differs depending on whether the multi-node function is used.

Value to substitute when the multi-node function is not used (kilobytes)

[Figure]

Value to substitute when the multi-node function is used (kilobytes)

[Figure]

Note:: If you define a base table or an index for a base table, the amount of process common memory (DB area information) that is required increases. Determine the value of the variable DBAREAINF again, according to (e) Determining the variable DBAREAINF in (3) Determining the process common memory requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.

(f) Determining the variable PROC_UPDSZ

Add the variable PROC_UPDSZ when you execute a definition SQL statement or update SQL statement for the first time after database connection. Use the following formula to determine its value.

Formula (kilobytes)

PROC_UPDSZ = BUFUPD × upd_users

Explanation of variables

upd_users

Number of concurrent executions of a connection (application program or command) for acquiring process common memory for managing updated pages

BUFUPD

Process common memory for managing updated pages

Use the following formula to determine its value.

Formula (kilobytes)

BUFUPD =
    ↑(56 + BUFHIST + BUFFLU + BUFBLK) ÷ 1,024↑

The following table shows the timing at which process common memory for managing updated pages is allocated.

Table 6‒4: Memory allocation timing
No.	Execution type		Allocated or not
1	Application program execution `adbsql` command execution	Connection in which a definition SQL statement is executed Connection in which an update SQL statement is executed	Y^#1
2	Application program execution `adbsql` command execution	Connection in which only a retrieval SQL statement is executed	N^{#2, #3}
3	`adbinit` command execution		Y
4	`adbimport` command execution		Y
5	`adbidxrebuild` command execution		Y
6	`adbgetcst` command execution		Y^#4
7	`adbdbstatus` command execution		N
8	`adbstat` command execution		N
9	`adbmodarea` command execution		Y
10	`adbexport` command execution		N^#2
11	`adbmergechunk` command execution		Y
12	`adbchgchunkcomment` command execution		Y^#4
13	`adbmodbuff` command execution		N
14	`adbarchivechunk` command execution		Y
15	`adbunarchivechunk` command execution		Y

Legend:

Y: Process common memory for managing updated pages is allocated.

N: Process common memory for managing updated pages is not allocated.

#1

If an error occurs during preprocessing, process common memory for managing updated pages is not allocated.

#2

Process common memory for managing updated pages is not allocated for writing data to a work table.

#3

If a definition SQL statement or update SQL statement is executed even once during a connection, the connection buffer remains allocated.

#4

Process common memory for managing updated pages is allocated in order to update the system tables.

BUFHIST

Use the following formula to determine its value.

Formula (bytes)

BUFHIST =
    116 × RTHNUM + 44,052 + BUFMEM(RTHNUM + 257) + BUFMEM(RTHNUM + 4,097)

BUFMEM

For details, see the description of the variable BUFMEM in (d) Determining the variable BUFGLOBAL under (3) Determining the process common memory requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

BUFFLU

Use the following formula to determine its value.

Formula (bytes)

BUFFLU =
    8 × uthd_num + 96 × RTHNUM + 393,312

uthd_num

Value specified for the adb_sys_uthd_num operand in the server definition

BUFBLK

Use the following formula to determine its value.

Formula (bytes)

BUFBLK =
    33,734,656 + 1,024 × BUFLOG

BUFLOG

For details, see the description of the variable BUFLOG in (d) Determining the variable BUFGLOBAL under (3) Determining the process common memory requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

(g) Determining the variable PROC_UPDLISTSZ

Add the variable PROC_UPDLISTSZ when you use the multi-node function. Use the following formula to determine the value.

Formula (kilobytes)

[Figure]

Explanation of variables

max_users: Value specified for the adb_sys_max_users operand in the server definition

To Page Top

(2) Determining the real thread private memory requirement (during normal operation)

Use the following formula to determine the real thread private memory requirement for normal operation (RTHD_EXECSZ).

Formula (kilobytes)

[Figure]

Explanation of variables

RTHD_CNCTSZ

Real thread private memory used when connecting to a database

Determine the value as explained in (a) Determining the variable RTHD_CNCTSZ.
RTHD_DEFSQLSZ

Real thread private memory used when executing definition SQL statements

Determine the value as explained in (b) Determining the variable RTHD_DEFSQLSZ.
RTHD_EXESQLSZ

Real thread private memory used when executing data manipulation SQL statements

Determine the value as explained in (c) Determining the variable RTHD_EXESQLSZ.
RTHD_ROLLBKSZ

Real thread private memory used during rollback processing or when restarting the HADB server

Determine the value as explained in (d) Determining the variable RTHD_ROLLBKSZ.
RTHD_DBEXTSZ

Real thread private memory used when extending a DB area

Determine the value as explained in (e) Determining the variable RTHD_DBEXTSZ.
RTHD_WORKBUF

Real thread private memory used when processing work tables

Determine the value as explained in (f) Determining the variable RTHD_WORKBUF.
RTHD_EXESQLDICSZ

Real thread private memory used when preprocessing data manipulation SQL statements

Determine the value as explained in (g) Determining the variable RTHD_EXESQLDICSZ.
RTHD_COMMUSZ

Real thread private memory used for communication processing

Determine the value as explained in (h) Determining the variable RTHD_COMMUSZ.
RTHD_MODASZ

Real thread private memory used for executing the adbmodarea command

Determine the value as explained in (2) Determining the real thread private memory requirement (for executing the adbmodarea command) under 6.3.12 Determining the memory requirement for executing the adbmodarea command.
RTHD_EXPSQLTRCSZ

Real thread private memory used when the SQL trace buffer is extended

Determine the value as explained in (i) Determining the variable RTHD_EXPSQLTRCSZ.
RTHD_AUDINFSZ

Real thread private memory used when the audit trail facility is enabled

Determine the value as explained in (j) Determining the variable RTHD_AUDINFSZ.
RTHD_COLUMNIZESZ

Real thread private memory used when the updated-row columnizing facility is enabled

Determine the value as explained in (k) Determining the variable RTHD_COLUMNIZESZ.

(a) Determining the variable RTHD_CNCTSZ

The variable RTHD_CNCTSZ is added when connecting to a database. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variable

TRANSNAP

For details, see the description of the variable TRANSNAP in (c) Determining the variable PROC_EXECSQLSZ under (1) Determining the process common memory requirement (during normal operation).

SQLTRC

Management information for SQL tracing

Assign 65,536 kilobytes.

(b) Determining the variable RTHD_DEFSQLSZ

The variable RTHD_DEFSQLSZ is required when executing definition SQL statements. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variable

SZ_ALTER_TABLE

Substitute the following value when you change the definition of a base table.

Value (kilobytes)

[Figure]

SZ_ALTER_VIEW

Substitute the following value when you change the definition of a viewed table.

Value (kilobytes)

[Figure]

SZ_CREATE_INDEX

Substitute the following value when you define an index.

Substitute the following value also when you include a uniqueness constraint definition or chunk-archive specification in the CREATE TABLE statement.

Value (kilobytes)

[Figure]

SZ_CREATE_TABLE

Substitute the following value when you define a base table.

Value (kilobytes)

[Figure]

SZ_CREATE_VIEW

Substitute the following value when you define a viewed table.

Value (kilobytes)

[Figure]

SZ_DROP_INDEX

Substitute the following value when you delete an index.

Substitute the following value also when an index is deleted as a result of processing after the DROP USER, DROP SCHEMA, or DROP TABLE statement is executed.

Value (kilobytes)

[Figure]

SZ_DROP_TABLE

Substitute the value shown below when you delete a base table.

Substitute the following value also when a base table is deleted as a result of processing after the DROP USER or DROP SCHEMA statement is executed.

Value (kilobytes)

[Figure]

SZ_DROP_VIEW

Substitute the following value when you delete a viewed table.

Substitute the following value also when a viewed table is deleted as a result of processing after the DROP USER, DROP SCHEMA, or DROP TABLE statement is executed.

Value (kilobytes)

[Figure]

SZ_GRANT

Substitute the following value when you grant a privilege.

Value (kilobytes)

[Figure]

SZ_REVOKE

Substitute the following value when you revoke a privilege.

Substitute the following value also when a privilege is revoked as a result of processing after the DROP USER, DROP SCHEMA, or DROP TABLE statement is executed.

Value (kilobytes)

[Figure]

(c) Determining the variable RTHD_EXESQLSZ

The variable RTHD_EXESQLSZ is required when executing data manipulation SQL statements. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

#: Add this value when executing the PURGE CHUNK or TRUNCATE TABLE statement for an archivable multi-chunk table.

Explanation of variables

max_sql()

For each SQL statement to be executed, calculate the result for the variable in the parentheses, and then substitute the maximum calculated value.

max_sql_concurrent_exec_num

Maximum number of SQL statements that will be executed at the same time during a transaction

SQL_CNCT_SIZE: Data manipulation SQL control area

Use the following formula to determine its value.

Formula (kilobytes)

SQL_CNCT_SIZE = STATEMENT_SIZE + CNCT_THD_SIZE + 0.3

STATEMENT_SIZE: Statement handle control area

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

PREPARE_INFO_SIZE: SQL statement preprocessing information

Use the following formula to determine its value.

Formula (bytes)

PREPARE_INFO_SIZE = col_num × 214 + param_num × 80 + idx_num × 512

col_num: Number of selection expressions specified in the SQL statement

param_num: Number of dynamic parameters specified in the SQL statement

idx_num: Number of indexes used by the SQL statement

PARAM_INFO_SIZE: Parameter storage area

Use the following formula to determine its value.

Formula (bytes)

[Figure]

param_num: Number of dynamic parameters specified in the SQL statement

param_size(i): Maximum data length of each dynamic parameter

APATHVIEW: Access path information control area that is created for displaying an access path

See the description of the variable APATHVIEW in (b) Determining the variable PROC_TOTALSQLSSZ under (1) Determining the process common memory requirement (during normal operation).

CNCT_THD_SIZE: SQL execution control area

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Important

■ Notes about specifying viewed tables and query names

When specifying a viewed table or query name in an SQL statement, estimate the memory requirements as if an internal derived table corresponding to each viewed table or query name were applied. For a recursive query name that references a target recursive query within a recursive query, estimate memory requirements assuming the work table that stores the results of the recursive query.

Example:

- SQL statement that defines the viewed table

CREATE VIEW "V1" AS SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C2"

- SQL statement to be executed

SELECT * FROM "V1","T3"
           WHERE "V1"."C1"="T3"."C3"

- SQL statement for which memory requirements are to be estimated

SELECT * FROM (SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C2"),"T3"
           WHERE "V1"."C1"="T3"."C3"

■ Notes when specifying an archivable multi-chunk table

When specifying an archivable multi-chunk table in an SQL statement, estimate memory requirements as if the archivable multi-chunk table were converted by equivalent exchange to a derived table. For details about the equivalent exchange of archivable multi-chunk tables, see Equivalent exchange of SQL statements that search archivable multi-chunk tables in the HADB Application Development Guide.

tbl_num: Number of tables specified

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

sql_rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the server definition

setop_num: Number of set operators specified in SQL statements

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

query_num: Number of query specifications

Determine this value from the number of FROM clauses specified in SQL statements.

If you specify FULL OUTER JOIN, add the number obtained by doubling the number of times FULL OUTER JOIN is specified.

window_num: Number of window functions specified in SQL statements

If multiple DISTINCT set functions are specified with different arguments in one query specification, also add the number of those set functions - 1.

drvtbl_num: Number of derived tables specified in SQL statements

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

tblfunc_num

Number of table function derived tables

Σ(HASHGRP_SIZE)

Sum total of the HASHGRP_SIZE values calculated for the individual queries that are specified in SQL statements (bytes)

Σ(LMTWRK_SIZE):

Sum total of the LMTWRK_SIZE values calculated for the individual work tables that are created (bytes)

For the following issues, see Considerations when executing an SQL statement that creates work tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide:

Whether an SQL statement in which the LIMIT clause is specified creates a work table
Number of work tables that are created

If the LIMIT clause is used to specify the maximum number of rows to be acquired from the results of a set operation, the value specified for the LIMIT clause is applied to each of the query specifications that comprise the set operation.

MAX(CSVREAD_WORK_SIZE):

Determine in bytes the value of the CSVREAD_WORK_SIZE variable for each table function derived table for which the ADB_CSVREAD function is specified. Then substitute the largest among the determined values.

tvc_num:

Number of table value constructors specified in SQL statements

in_query_num:

Total number of predicates that satisfy the following conditions:

IN predicate

A table subquery is specified on the right side, and the column specified on the left-side value expression is the first of the B-tree indexed columns to be used for a search.
Quantified predicate

The column specified on the left-side value expression in = ANY or = SOME is the first of the B-tree indexed columns to be used for a search.

RESULT_WORK_SIZE: SQL execution result storage area

Use the formula shown below to determine its value. To calculate the data length, see Table 6‒9: Data length of each data type.

Formula (bytes)

RESULT_WORK_SIZE =
    ↑(40 + sum_col_size + row_size
    + 2 × (sum_grp_size + sum_window_spec_size)
    + 3 × (sum_set_func_size + sum_dist_col_size + sum_window_func_size)
    + 8 × (all_col_num + row_num + 2 × (grp_col_num + window_spec_col_num)
    + 3 × (set_func_num + dist_col_num + window_func_num))
    + sum_inpred_size + sum_reg_size
    + 8 × group_query_num + 12 × list_num
    + 16 × join_num + supquery_num
    + sum_setop_size × 3 + full_join_drvc_size × 3
    + (func_filepath_size + 4) × (csvread_num + auditread_num)) ÷ 16↑ × 16

sum_col_size

Sum total of the data lengths of all specified columns
row_size

Sum total of the row lengths of the table from which data is acquired using ROW
sum_grp_size

Sum total of the data lengths of the grouping columns
sum_window_spec_size

Sum total of the data lengths of the columns specified in window specifications
sum_set_func_size

Sum total of the data lengths of all set function results
sum_dist_col_size

Sum total of the data lengths of the columns specified as arguments in the DISTINCT set function
sum_window_func_size

Sum total of the data length values of the results of window functions
all_col_num

Number of all columns specified
row_num

Number of ROWs specified
grp_col_num

Number of grouping columns specified in the GROUP BY clause
window_spec_col_num

Number of columns specified in window specifications
set_func_num

Number of set functions
dist_col_num

Number of columns specified as arguments of the DISTINCT set function
window_func_num

Number of window functions
sum_inpred_size

Sum total of the sizes of the result creation areas of all IN predicates specified (29 + 8 × the-number-of-value-expressions-specified-on-the-right-side-of-the-IN-predicate). The value is 0 if no IN predicate is specified.
sum_reg_size

Sum total of the data length for all datetime information acquisition functions and user information acquisition functions specified
group_query_num

Number of query specifications in which the GROUP BY clause is specified
list_num

Number of work tables

Substitute the combined total of the following values:

• + 1 if the ORDER BY clause is specified

• Number of query specifications in which the GROUP BY clause is specified

• Number of query specifications in which SELECT DISTINCT is specified

• Number of times the DISTINCT set function is specified

• Number of times the inverse distribution function is specified

• Number of times the window function is specified

• Number of query specifications in which multiple table references are specified in the FROM clause

• Number of derived tables

• Number of viewed tables specified

• Number of WITH clauses

• Number of joined tables specified (to be counted as 2 in the case of FULL OUTER JOIN)

• Number of subqueries specified

• Number of set operations specified + 1
join_num

Number of joined tables

Count each table as 2 in the case of FULL OUTER JOIN.
supquery_num

Number of subqueries
sum_setop_size

For each set operation, total the data lengths of the selection expressions that result from the set operation. Then, substitute the value obtained by adding up these lengths.
full_join_drvc_size

For each query specification in which FULL OUTER JOIN is specified, determine the data length of the selection expression. Then, substitute the value obtained by adding up these lengths.
func_filepath_size

Maximum length of file path names specified in ADB_CSVREAD functions and ADB_AUDITREAD functions
csvread_num

Number of ADB_CSVREAD functions
auditread_num

Number of ADB_AUDITREAD functions

OPE_WORK_SIZE: Operation control area

Use the formula shown below to determine its value. However, its value is 0 if no arithmetic operations, logical operations, CASE expressions, or scalar functions are specified. To calculate the data length, see Table 6‒9: Data length of each data type.

Formula (bytes)

[Figure]

arith_num: Maximum number of arithmetic operators (+, -, * or /) in each value expression

concat_num: Maximum total number of concatenation operators (+ or ||) and CONCAT scalar functions in value expressions

logical_num: Maximum number of logical operators in search conditions

case_size: Sum total of the result length values of CASE expressions

sclfunc_size: Sum total of the result length values of scalar functions

HASHGRP_SIZE: Hash group area

Use the formula shown below to determine its value. However, its value is 0 if 0 is specified for the adb_sql_exe_hashgrp_area_size operand in the server definition or if no GROUP BY clause or set function is specified in the SQL statement. To calculate the data length, see Table 6‒9: Data length of each data type.

Formula (bytes)

HASHGRP_SIZE =
  160 + 24 × (grp_col_num + set_func_num) + sum_grp_size
  + sum_set_func_size + hashgrp_byte
  + ↓(hashgrp_byte × 4 - MIN(8,192 × 128,
    ↓(hashgrp_byte × 10) ÷ 25↓))
  ÷ (↑(16 + ↑(sum_grp_size) ÷ 4↑ × 4
  + sum_set_func_size) ÷ 8↑ × 8)↓

grp_col_num

Number of grouping columns specified in the GROUP BY clause
set_func_num

Number of set functions
sum_grp_size

Sum total of the data lengths of the grouping columns
sum_set_func_size

Sum total of the data lengths of all set function results

hashgrp_byte

Value specified for the hash group area size

Use the following formula to determine its value:

value-specified-for-adb_sql_exe_hashgrp_area_size-operand-in-server-definition × 1,024

HASHTBL_CNCT_SIZE: Management area when retrieval is performed using a hash table

Use the formula shown below to determine its value if the SQL statement contains any of the items listed here. Note that the value is 0 if the SQL statement contains none of these items. The value is also 0 when the maximum number of SQL processing real threads or the hash table area size is 0.

Query specification that uses the equal sign (=) to join multiple tables
Subquery
GROUP BY clause
DISTINCT set function
SELECT DISTINCT
UNION or UNION DISTINCT

Formula (bytes)

[Figure]

sql_rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the server definition
hashtbl_row_num

Maximum row length in hash tables

For details, see 6.25.3 Number of work tables created during retrieval using hash tables.
hashtbl_col_num

Substitute the maximum value from among the values determined according to the specifications within the SQL statements.

■ Query specification that uses the equal sign (=) to join multiple tables

Maximum number of columns in a hash table. When you are determining the number of columns in a hash table in each query specification, for each of the tables joined by the equal sign (=), determine the total number of columns specified in selection expressions and search conditions. Then determine the total value, excluding the minimum value, for each table for which you determined the number of columns.

■ Subquery

For each subquery, determine the sum total of the values listed below. Use the largest value among these summed values as the subquery value.

• Number of columns specified in a subquery selection expression

• Number of set functions specified in a subquery

• Number of columns specified for predicates that include external reference columns within the search condition

■ GROUP BY clause and the DISTINCT set function

For each query in which the GROUP BY clause and the DISTINCT set function are specified, determine the sum total of the values listed below. Use the largest value among these summed values as the value for the GROUP BY clause and the DISTINCT set function.

• Number of grouping columns specified in the GROUP BY clause

• Number of set functions

■ SELECT DISTINCT

Determine the following value for each query specification that includes SELECT DISTINCT. Use the largest of the determined values as the value of SELECT DISTINCT.

• Number of columns specified in a selection expression

■ UNION or UNION DISTINCT

Determine the following value for each query expression body in which UNION or UNION DISTINCT is specified. Use the largest of the determined values as the value of UNION or UNION DISTINCT.

• Number of columns in the table that is derived by the query expression body
bucket_num

Number of work tables created during retrieval using hash tables

For details, see 6.25.3 Number of work tables created during retrieval using hash tables.
hashtbl_num

Number of hash tables

For details, see 6.25.3 Number of work tables created during retrieval using hash tables.

HASHFLT_CNCT_SIZE: Hash filter management area

Determine this value if either of the following items is included in the SQL statement. If neither of the items is included, this value is 0. This value is also 0 if the size of the hash filter area (value specified for the adb_sql_exe_hashflt_area_size operand in the server definition) is 0.

Query specification that uses the equal sign (=) to join multiple tables
Subquery

Formula (bytes)

HASHFLT_CNCT_SIZE = 256 + 256 × hashflt_num

hashflt_num

Total number of hash filters in the SQL statement

Determine the total of all the following values:

• Number of join conditions that use = specifications in query specifications that join multiple tables by using equal signs (=)

• Number of subqueries that do not include any external reference columns

• Number of = conditions that include external reference columns in subqueries that include external reference columns

HASHRNG_CNCT_SIZE: Management area for evaluation of range index conditions in hash retrieval

Determine this value if either of the following items is included in the SQL statement. If neither of the items is included, this value is 0.

Query specification that uses the equal sign (=) to join multiple tables
Subquery

Formula (bytes)

HASHRNG_CNCT_SIZE = 64 + 136 × hash_retrieval_rngidx_num + 56 × hash_cond_rngidx_num

hash_retrieval_rngidx_num

Total number of hash retrieval processes that use a range index in the SQL statement

Determine the total of all the following values. If this total number is 0, the value of HASHRNG_CNCT_SIZE is 0.

The value determined from the following formula for the query specifications in which a range-indexed column is specified for the equal sign (=), of the query specifications that use the equal sign (=) to join multiple tables:
```
total-number-of-tables-joined-by-using-equal-sign - total-number-of-query-specifications-joined-by-using-equal-sign
```
Number of quantified predicates for which a range-indexed column is specified on the left side
Number of IN predicates for which a range-indexed column is specified on the left side and a table subquery is specified on the right side
Number of subqueries (other than the preceding two subqueries) that satisfy all of the following conditions:

• An external reference column is specified by using the equal sign (=) in a search condition

• The specified external reference column is a range-indexed column

hash_cond_rngidx_num

Total number of conditions that use a range index for the hash retrieval processes in the SQL statement

Determine the total of all the following values:

Number of = join conditions in which range-indexed columns are specified in the query specifications that use the equal sign (=) to join multiple tables
Number of quantified predicates for which a range-indexed column is specified on the left side
Number of IN predicates for which a range-indexed column is specified on the left side and a table subquery is specified on the right side
Number of = conditions (other than the preceding two subqueries) that satisfy all of the following conditions:

• An external reference column is specified by using the equal sign (=) in a search condition

• The specified external reference column is a range-indexed column

GRSETS_CNCT_SIZE: Work area for the DISTINCT set function

Determine this value if the SQL statement includes a query specification in which multiple DISTINCT set functions are specified with different arguments. The value of this variable is 0 if the SQL statement does not include a query specification in which multiple DISTINCT set functions are specified with different arguments.

Formula (bytes)

GRSETS_CNCT_SIZE =
 (616 + 136 × dset_func_num + sql_rthd_num × (664 + 72 × dset_func_num)
   ) × dset_query_num

dset_func_num: Number of DISTINCT set functions specified with different arguments

sql_rthd_num: The maximum number of SQL processing real threads (the value specified for the adb_sql_exe_max_rthd_num operand in the server definition).

dset_query_num: Number of query specifications in which DISTINCT set functions are specified with different arguments

IN_PRD_SIZE: IN predicate and quantified predicate execution management area

Use the formula shown below to determine its value. However, the value is 0 if no IN predicate or quantified predicate is specified. The variable IN_PRD_SIZE is 0 also if the variable in_pred_num is 0.

Formula (bytes)

IN_PRD_SIZE = 48 + in_pred_num × (96 + sql_rthd_num × 48)

sql_rthd_num: Value specified for the adb_sql_exe_max_rthd_num operand in the server definition

in_pred_num: Total number of predicates that satisfy the following conditions:

IN predicate

The column specified on the left-side value expression is the first of the B-tree indexed columns to be used for a search.
Quantified predicate

The column specified on the left-side value expression in = ANY or = SOME is the first of the B-tree indexed columns to be used for a search.

LIKE_PRD_SIZE: LIKE predicate execution control area

Use the formula shown below to determine its value. Note that the value is 0 if no LIKE predicate is specified. The value is also 0 if the column specified as the match value of all LIKE predicates is not included in the B-tree indexed columns to be used for a search. To calculate the data length, see Table 6‒9: Data length of each data type.

Formula (bytes)

LIKE_PRD_SIZE = 16 + like_param_num × (64 + like_param_size × 2)

like_param_num

Number of LIKE predicates in which the dynamic parameter is specified for the pattern character string. If the column specified as the match value of a LIKE predicate is not included in the B-tree indexed columns to be used for a search, that LIKE predicate is excluded from the count.
like_param_size

Maximum data length assumed by the dynamic parameter specified as the pattern character string. If the column specified as the match value of a LIKE predicate is not included in the B-tree indexed columns to be used for a search, that LIKE predicate is excluded from the count.

REG_SIZE: Management area of the datetime information acquisition function and the user information acquisition function

Use the formula shown below to determine its value. Note that the value is 0 if neither a datetime information acquisition function nor a user information acquisition function are specified. To calculate the data length, see Table 6‒9: Data length of each data type.

Formula (bytes)

REG_SIZE = 24 × date_spec + 32 × time_spec + 32 × timestmp_spec + 120 × user_spec + 24

date_spec

A value of 1 if CURRENT_DATE is specified; 0 otherwise
time_spec

A value of 1 if CURRENT_TIME is specified; 0 otherwise
timestmp_spec

A value of 1 if CURRENT_TIMESTAMP is specified; 0 otherwise
user_spec

A value of 1 if CURRENT_USER is specified; 0 otherwise

LMTWRK_SIZE

Work table control area

The value is 0 if a LIMIT clause is not specified or no work table is created.

Formula (bytes)

LMTWRK_SIZE =
  ↑(96 × ↑(offset_num + limit_num) ÷ ↓(wrk_page_size - 64) ÷ ROWSZ↓↑
  + (4 + ROWSZ) × (offset_num + limit_num)) ÷ 1,024↑

offset_num

Offset of the first row to return (offset) as specified in a LIMIT clause
limit_num

Maximum number of rows to return (row-count) as specified in a LIMIT clause
wrk_page_size

Page size of the work table DB area (bytes)

See Table 6‒3: DB area page size in (2) Determining the global buffer page requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.
ROWSZ

Row length of a work table (bytes)

Determine the row length of the work table based on the formula for the variable ROWSZ in 5.9.2 Determining the number of pages for base rows that are needed for storing work tables. However, when the variable col_size₍i₎ needs to be determined and the column is of the variable-length data type, substitute n (definition length) for d (actual data length).

COR_QUERY_SIZE

Work area for subqueries that contain external reference columns

Substitute 16,777,216 bytes. If no subquery containing external reference columns is specified, assign 0.

TXT_WORK_SIZE

Work area for text indexes

Use the following formula to determine its value. If no text index is defined or used, assign 0.

Formula (bytes)

[Figure]

scan_str_num

Number of characters in the pattern character string specified in the LIKE predicate that uses a text index

CSVREAD_WORK_SIZE

Retrieval work area for the ADB_CSVREAD function

Determine this value from the following formula. If the ADB_CSVREAD function will not be used, assign 0.

Formula (bytes)

[Figure]

decomp_areasize

Decompression area size (bytes)

Size of area for decompressing input data files. The value to be substituted differs according to the value specified for the compression format option COMPRESSION_FORMAT of the ADB_CSVREAD function.

To determine the decompression area size, see the following table.

Table 6‒5: List of the values that can be specified as the decompression area size
No.	Value of COMPRESSION_FORMAT	Decompression area size (bytes)
1	`GZIP`	112
2	`NONE`	40

col_num

Number of field data items that are to be retrieved from input data files

temp_coldata_areasize(i)

Temporary storage area size (bytes)

The size of the area that is used to temporarily store the data of the i-th field retrieved from the input data file. The value of i corresponds to the sequence number of the column name list of the table-function derived table.

For the temporary storage area size, use the value shown in the following table.

Table 6‒6: Temporary storage area size for each data type
No.	Classification	data type		Temporary storage area size (bytes)
1	Numeric data	`INTEGER`		24
2		`SMALLINT`		16
3		`DECIMAL`		48
4		`DOUBLE` `PRECISION`		512
5	Character string data	`CHAR`		((2 + `defined length` × 2 + 7) ÷ 8) × 8
6	Character string data	`VARCHAR`		((2 + `defined length` × 2 + 7) ÷ 8) × 8
7	Datetime data	`DATE`		16
8		`TIME`		24
9		`TIMESTAMP`		40
10	Binary data	`BINARY`	Input data file in hexadecimal format	((2 + `defined length` × 2 + 7) ÷ 8) × 8
11		`BINARY`	Input data file in binary format	((2 + `defined length` × 8 + 7) ÷ 8) × 8
12		`VARBINARY`	Input data file in hexadecimal format	((2 + `defined length` × 2 + 7) ÷ 8) × 8
13		`VARBINARY`	Input data file in binary format	((2 + `defined length` × 8 + 7) ÷ 8) × 8

REGEXP_WORK_SIZE

Size of the work area that is used to evaluate regular expressions (bytes)

If the LIKE_REGEX predicate is not specified, substitute 0.

The formula for calculating the value of the variable REGEXP_WORK_SIZE differs depending on whether a zero-character repetition is included in the regular expression specified as the pattern character string for the LIKE_REGEX predicate.

A zero-character repetition is a specification that might refer to a zero character as a result of combining meta characters and repetition factors. For example, if you specify *, ?, {0,}, and {0,x} (x: 1 or a larger integer) in a nested form, the specification becomes a zero-character repetition.

A specification example that becomes a zero-character repetition

(a*)+, (c{0,2})*, (e?){3}

The following shows the formula for determining the variable REGEXP_WORK_SIZE.

If a zero-character repetition is not included in the regular expression:

[Figure]

If a zero-character repetition is included in the regular expression:

[Figure]

Important: If a zero-character repetition is included in the regular expression, a large amount of memory might be consumed. However, even if there is a specification that might refer to a zero character, a large amount of memory is not consumed unless quantifiers or repetition factors are nested.

push_instr_num

Use the following formula to determine the value.

[Figure]

For details about how to determine the variable C (number of repetitions specified for a repetition factor), see the following table.

Table 6‒7: Determining the number of repetitions specified for a repetition factor
No.	Specification of a repetition factor	Value to be substituted for the variable C	Example
1	The upper and lower limits are both specified.	Value obtained by upper-limit - lower-limit	If `{10,20}` is specified, substitute 10.
2	The following conditions are met: Only the lower limit is specified. The lower limit is not followed by a comma.	Lower-limit	If `{10}` is specified, substitute 10.
3	The following conditions are met: Only the lower limit is specified. The lower limit is followed by a comma.	Value obtained by 256 - lower-limit	If `{10,}` is specified, substitute 246.

scan_str_num

Number of characters in the character string specified as the pattern character string

ostrrep_instr_num

Total value of zero-character repetitions

The following shows examples of regular expressions and formulas for determining the variable REGEXP_WORK_SIZE.

Regular expression specification example 1

KFAA11[0-9]{3}-E

Formula example (bytes)

[Figure]

Regular expression specification example 2

KFAA6000[2-9]-E.*uid=12345.*

Formula example (bytes)

[Figure]

RECURSIVE_QUERY_CNCT_SIZE

Recursive query management area (bytes)

If the SQL statements will not contain recursive queries, specify 0.

[Figure]

sql_rthd_num

The maximum number of SQL processing real threads (the value specified for the adb_sql_exe_max_rthd_num operand in the server definition).

recursive_query_num

Number of recursive queries specified in SQL statements

recurmb_query_num

Number of FROM clauses specified in recursive members

recurmb_setop_num

Number of set operators specified in recursive members

recurmb_list_num

Sum total of all the following specified in recursive members

Number of times the window function is specified
Number of query specifications in which multiple table references are specified in the FROM clause
Number of joined tables specified
Number of subqueries specified

recurmb_subquery_num

Number of subqueries specified in recursive members

recurmb_hashtbl_num

Number of hash tables specified in recursive members

For details, see Work tables created when SQL statements are executed in the HADB Application Development Guide.

RANDOMCURSOR_WORK_SIZE

Work area for scalar function RANDOMCURSOR

Substitute the following value if scalar function RANDOMCURSOR is included in the SQL statement. Substitute 0 if scalar function RANDOMCURSOR is not included in the SQL statement.

Value (bytes)

RANDOMCURSOR_WORK_SIZE = 3,176

RANDOM_NUM_WORK_SIZE

Work area for generating pseudorandom numbers (bytes)

RANDOM_NUM_WORK_SIZE = RANDOM_NUM_UNIFORM_WORK_SIZE
                     + RANDOM_NUM_NORMAL_WORK_SIZE
                     + RANDOMROW_WORK_SIZE

RANDOM_NUM_UNIFORM_WORK_SIZE

Work area for generating pseudorandom numbers according to the uniform distribution

If the SQL statement includes a RANDOM scalar function, substitute the following value. If the SQL statement does not include a RANDOM scalar function, substitute 0.

Value (bytes)

RANDOM_NUM_UNIFORM_WORK_SIZE = 3,176

RANDOM_NUM_NORMAL_WORK_SIZE

Work area for generating pseudorandom numbers according to the normal distribution

If the SQL statement includes a RANDOM_NORMAL scalar function, substitute the following value. If the SQL statement does not include a RANDOM_NORMAL scalar function, substitute 0.

Value (bytes)

RANDOM_NUM_NORMAL_WORK_SIZE = 6,288

RANDOMROW_WORK_SIZE

Work area for scalar function RANDOMROW

Substitute the following value if scalar function RANDOMROW is included in the SQL statement. Substitute 0 if scalar function RANDOMROW is not included in the SQL statement.

Value (bytes)

RANDOMROW_WORK_SIZE = 3,176

PROC_THD_SIZE: SQL process real thread control area

Use the formula shown below to determine its value. For variables RESULT_WORK_SIZE, OPE_WORK_SIZE, HASHGRP_SIZE, COR_QUERY_SIZE, REGEXP_WORK_SIZE, and RANDOM_NUM_WORK_SIZE, substitute the values determined from the formulas described earlier.

Formula (kilobytes)

[Figure]

Important

Notes when specifying viewed tables and query names

When specifying a viewed table or query name in an SQL statement, estimate the memory requirements as if an internal derived table corresponding to each viewed table or query name were applied. For a recursive query name that references a target recursive query within a recursive query, estimate memory requirements assuming the work table that stores the results of the recursive query.
Notes when specifying an archivable multi-chunk table

When specifying an archivable multi-chunk table in an SQL statement, estimate memory requirements as if the archivable multi-chunk table were converted by equivalent exchange to a derived table. For details about the equivalent exchange of archivable multi-chunk tables, see Equivalent exchange of SQL statements that search archivable multi-chunk tables in the HADB Application Development Guide.

tbl_num: Number of tables specified

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

sql_rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the server definition

idx_col_num

Number of B-tree-indexed columns and text-indexed columns to be used for a search

idx_size

Sum of the defined lengths of all B-tree-indexed columns and text-indexed columns that will be used

uthd_num

Value specified for the adb_sys_uthd_num operand in the server definition

Σ_[i =cor_query](HASHGRP_SIZE(i)):

Sum total of the HASHGRP_SIZE values calculated for all subqueries that contain external reference columns in SQL statements (bytes)

Σ_[j =not_cor_query](HASHGRP_SIZE(j)):

Sum total of the HASHGRP_SIZE values calculated for query specifications that are not subqueries containing external reference columns in SQL statements (bytes)

HASHTBL_PROC_SIZE: Real thread management area during retrieval using hash tables

Determine this value if the SQL statement contains any of the items listed below. Note that the value is 0 if the SQL statement contains none of these items. The value is also 0 when the maximum number of SQL processing real threads or the hash table area size is 0.

Query specification that uses the equal sign (=) to join multiple tables
Subquery
GROUP BY clause
DISTINCT set function
SELECT DISTINCT
UNION or UNION DISTINCT

Formula (bytes)

[Figure]

hashtbl_byte

Value determined from the following formula:

[Figure]

sql_rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the client definition
uthd_num

Value specified for the adb_sys_uthd_num operand in the server definition
clt_wrk_page_num

Value specified for the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition (Value specified for the export option adb_export_wrktbl_blk_num when the adbexport command is to be executed)
hashtbl_num

Number of hash tables

For details, see 6.25.3 Number of work tables created during retrieval using hash tables.

HASHFLT_PROC_SIZE: Hash filter management area for the SQL processing real thread

Query specification that uses the equal sign (=) to join multiple tables
Subquery

Formula (bytes)

HASHFLT_PROC_SIZE = ↑hashflt_byte ÷ sql_rthd_num↑ + 136 + 192 × hashflt_num

hashflt_byte

Value determined from the following formula:

Value-specified-for-adb_sql_exe_hashflt_area_size-in-server-definition × 1,024 × 1,024

sql_rthd_num

Value specified for the adb_sql_exe_max_rthd_num operand in the client definition
hashflt_num

Total number of hash filters in the SQL statement

Determine the total of all the following values:

• Number of join conditions that use = specifications in query specifications that join multiple tables by using equal signs (=)

• Number of subqueries that do not include any external reference columns

• Number of = conditions that include external reference columns in subqueries that include external reference columns

HASHRNG_PROC_SIZE: Management area for evaluation of range index conditions for the SQL processing real thread in hash retrieval

Determine this value if either of the following items is included in the SQL statement. If neither of the items is included, this value is 0.

Query specification that uses the equal sign (=) to join multiple tables
Subquery

Formula (bytes)

HASHRNG_PROC_SIZE = 64 + 72 × hash_retrieval_rngidx_num + 64 × hash_cond_rngidx_num
                  + Σ(↑(hash_cond_rngidx_len) ÷ 16↑ × 32)

hash_retrieval_rngidx_num

Total number of hash retrieval processes that use a range index in the SQL statement

Determine the total of all the following values. If this total number is 0, the value of HASHRNG_PROC_SIZE is 0.

The value determined from the following formula for the query specifications in which a range-indexed column is specified for the equal sign (=), of the query specifications that use the equal sign (=) to join multiple tables:
```
total-number-of-tables-joined-by-using-equal-sign - total-number-of-query-specifications-joined-by-using-equal-sign
```
Number of quantified predicates for which a range-indexed column is specified on the left side
Number of IN predicates for which a range-indexed column is specified on the left side and a table subquery is specified on the right side
Number of subqueries (other than the preceding two subqueries) that satisfy all of the following conditions:

• An external reference column is specified by using the equal sign (=) in a search condition

• The specified external reference column is a range-indexed column

hash_cond_rngidx_num

Total number of conditions that use a range index for the hash retrieval processes in the SQL statement

Determine the total of all the following values:

Number of = join conditions in which range-indexed columns are specified in the query specifications that use the equal sign (=) to join multiple tables
Number of quantified predicates for which a range-indexed column is specified on the left side
Number of IN predicates for which a range-indexed column is specified on the left side and a table subquery is specified on the right side
Number of = conditions (other than the preceding two subqueries) that satisfy all of the following conditions:

• An external reference column is specified by using the equal sign (=) in a search condition

• The specified external reference column is a range-indexed column

Σ(↑(hash_cond_rngidx_len) ÷ 16↑ × 32): Determine the value for each condition that uses a range index for the hash retrieval processes in the SQL statement, and then sum the determined values.

hash_cond_rngidx_len

Determine the following value for each condition that uses a range index for hash retrieval:

The larger of the data lengths of value expressions specified on both sides of the = join condition for which range-indexed columns are specified in the query specifications that use the equal sign (=) to join multiple tables
The larger of the following values for the quantified predicate for which a range-indexed column is specified on the left side:

• Total value of data lengths of results of selection expressions for the table subquery specified on the right side

• Column length of the range-index column specified on the left side
The larger of the following values for the IN predicates for which a range-indexed column is specified on the left side and a table subquery is specified on the right side:

• Total value of data lengths of results of selection expressions for the table subquery specified on the right side

• Column length of the range-index column specified on the left side
The larger of the data lengths of results of value expressions on both sides of the = condition specified in the search condition in the subquery (that is not any of the preceding two subqueries and satisfies the following two conditions):

• An external reference column is specified by using the equal sign (=) in the search condition in the subquery

• The specified external reference column is a range-indexed column

setop_num: Number of set operators specified in SQL statements

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

drvtbl_num: Number of derived tables specified in SQL statements

If you specify FULL OUTER JOIN, add the number of times FULL OUTER JOIN is specified.

in_query_num: Sum total of the number of IN predicates and quantified predicates that satisfy the following conditions:

IN predicate

A table subquery is specified on the right side, and the column specified on the left-side value expression is the first of the B-tree indexed columns to be used for a search.
Quantified predicate

The column specified on the left-side value expression in = ANY or = SOME is the first of the B-tree indexed columns to be used for a search.

inprd_cor_query_num:

Sum total of the number of IN predicates and quantified predicates that are specified in subqueries that contain external reference columns, and that satisfy the condition specified in the variable in_query_num.

max_cor_query(X):

For each subquery that contains external reference columns in an SQL statement, substitute the maximum value of X specified in the subquery.

setop_cor_query_num:

Number of set operators specified in subqueries that contain external reference columns

window_cor_query_num: Number of window functions specified in subqueries that contain external reference columns

In subqueries that contain external reference columns, if multiple DISTINCT set functions are specified with different arguments in one query specification, also add the number of those set functions - 1.

limit_cor_query_num:

Number of LIMIT clauses specified in subqueries that contain external reference columns

cor_query_num:

Sum total of the number of subqueries that contain external reference columns and the number of subqueries contained in those subqueries

Σ(drvtbl_row_len × MIN(drvtbl_row_num, 1,044,480)):

If you specify derived tables, determine the value of the expression drvtbl_row_len × MIN(drvtbl_row_num, 1,044,480) for each derived table. Then, add up the determined values.

drvtbl_row_len

Substitute the value obtained by adding up the data lengths of the selection expressions of the derived tables.

To calculate the data length, see Table 6‒9: Data length of each data type.
drvtbl_row_num

Substitute the number of rows in the derived table. If you cannot determine the number of rows, substitute 1,044,480.

Σ(in_query_drvc_len × MIN(in_query_drvc_num, 1,044,480)):

Determine the value of the expression in_query_drvc_len × MIN(in_query_drvc_num, 1,044,480) for each predicate targeted by the variable in_query_num. Then, add up the determined values.

in_query_drvc_len

Substitute the data length of the selection expression in the subquery of the predicate targeted by the variable in_query_num.

To calculate the data length, see Table 6‒9: Data length of each data type.
in_query_drvc_num

Substitute the number of rows in the subquery of the predicate targeted by the variable in_query_num. If you cannot determine the number of rows, substitute 1,044,480.

leftouter_num

Number of times LEFT OUTER JOIN is specified

Σ(LMTWRK_SIZE):

Sum total of the LMTWRK_SIZE values calculated for the individual work tables that are created (bytes)

Whether an SQL statement in which the LIMIT clause is specified creates a work table
Number of work tables that are created

tblfunc_num

Number of table function derived tables

Σ(tblfunc_filename_len + 5)

Determine the value of the expression tblfunc_filename_len + 5 for each table function derived table. Then, add up the determined values.

tblfunc_filename_len

Length of the file name (full path) of the file to be accessed

Σ((tblfunc_row_len + 4) × MIN(tblfunc_row_num, 65,025))

Determine the value of the expression (tblfunc_row_len +4) × MIN(tblfunc_row_num, 65,025) for each table function derived table. Then, add up the determined values.

tblfunc_row_len

Sum of the data lengths for all columns in table function derived tables

To calculate the data length, see Table 6‒9: Data length of each data type.

tblfunc_row_num

Number of rows in table function derived tables

If you cannot determine the number of rows, substitute 65,025.

Σ(CSVREAD_WORK_SIZE)

Determine in bytes the value of the CSVREAD_WORK_SIZE variable for each table function derived table for which the ADB_CSVREAD function is specified. Then, add up the determined values.

Σ(AUDITREAD_WORK_SIZE)

Determine in bytes the value of the AUDITREAD_WORK_SIZE variable for each table function derived table for which the ADB_AUDITREAD function is specified. Then, add up the determined values.

query_num

Number of queries

Determine this value from the number of FROM clauses specified in SQL statements.

If you specify FULL OUTER JOIN, add the number obtained by doubling the number of times FULL OUTER JOIN is specified.

tvc_num

Number of table value constructors specified in SQL statements

SCAN_WORK_SIZE: Search work area

Use the following formula to determine its value.

Formula (bytes)

SCAN_WORK_SIZE = MAX(SCB_SIZE) + 24

MAX(SCB_SIZE): The maximum size of the work area for internal retrieval processing

Determine the value of the SCB_SIZE variable for each table to be searched. Then, substitute the largest of the determined values.

SCB_SIZE: Work area for internal retrieval processing

Use the following formula to determine the value:

Formula (bytes)

[Figure]

KEYSZ: Key length of the B-tree index to be used for searching

For details, see 5.8.4 Determining the key length (KEYSZ) of a B-tree index.

idx_col_num

Number of B-tree indexed columns to be used for a search

logical_num

Maximum number of logical operators in a search condition

scan_str_num

Substitute either of the following values:

Number of characters in the pattern character string specified in the LIKE predicate that uses a text index
Number of characters in the search string specified for the scalar function CONTAINS

Note that if you will perform a word-based leading-match word search with the WORDCONTEXT_PREFIX word-context search specification specified in a CONTAINS scalar function, substitute a value equivalent to the number of words specified in the search string × 32.

If the pattern character string consists of one character or more than 1,000 characters, assign 1,000.

txt_nest_num

Total of the following numbers of nests:^#

Number of nested OR conditions in the LIKE predicate that uses a text index
Number of nested OR conditions in the scalar function CONTAINS

txt_contains_str

Number of characters in the search string specified for the scalar function CONTAINS^#

#

The maximum of the total of the values specified for the variables txt_nest_num and txt_contains_str is 1,001. If the total value exceeds 1,001, substitute 1,001.

CS_WORK_SIZE: Work area for retrieval from column store tables

Use the following formula to determine the value: Note that the value is 0 if data will not be retrieved from any column store tables.

Formula (bytes)

[Figure]

col_num

Number of selection expressions

in_projcol_num

The number of column names specified in the value expression on the right side of the IN predicate

If no IN predicate is specified or there are no column names specified in the value expression on the right side of the IN predicate, substitute 0.

tbl_col_num

Number of columns in the table

SEGSIZE

Segment size in the DB area in which DB area files are defined (pages)

Use the following formula to determine the segment size:

SEGSIZE = 4,194,304 ÷ page_size

page_size

Page size of the DB area defined for the table (bytes)

See Table 6‒3: DB area page size in (2) Determining the global buffer page requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.

pred_num

Number of predicates

If no search conditions are specified, substitute 1.

branch_col_size

Maximum definition length of columns whose data type is character string data or binary data (bytes)

If there is a column with a definition length of 128 or longer among those defined in selection expressions or search conditions, substitute the maximum definition length.

Σ(DECOMPRESS_WORK_SIZE)

Determine the value of the variable DECOMPRESS_WORK_SIZE for each column specified in selection expressions and search conditions. Then, add up the determined values.

grp_col_num

Number of columns specified in the GROUP BY clause

Σ(↑grp_col_size ÷ 8↑ × 8)

Determine the value of the expression ↑grp_col_size ÷ 8↑ × 8 for each column specified in the GROUP BY clause, and then add up the determined values.

grp_col_size

Data length of each column specified in the GROUP BY clause

To calculate the data length, see Table 6‒9: Data length of each data type.

GROUPING_IN_SGMT_WORK_SIZE

Grouping work area in the segment

Use the following formula to determine this value. If the GROUP BY clause is not specified, substitute 0.

Formula (bytes)

[Figure]

grp_col_num

Number of columns specified in the GROUP BY clause

set_func_num

Number of set functions

grouping_col_data_size(i)

Data length of each column

Determine the data length from the following table.

Table 6‒8: Value to be substituted for grouping_col_data_size
No.	Classification	data type		Data length (bytes)
1	Numeric data	`INTEGER`		8
2		`SMALLINT`		8
3		`DECIMAL`	1 ≤ `precision` ≤ 4	8
4			5 ≤ `precision` ≤ 8
5			9 ≤ `precision` ≤ 16
6			17 ≤ `precision` ≤ 38	16
7		`DOUBLE` `PRECISION`		8
8	Character string data	`CHAR`		`definition-length` + 7
9		`VARCHAR`	1 ≤ `definition-length` ≤ 32,000	`definition-length` + 9
10		`VARCHAR`	32,001 ≤ `definition-length` ≤ 64,000	`definition-length` + 11
11	Datetime data	`DATE`		8
12		`TIME(p)`		3 + ↑`p` ÷ 2↑ + 7
13		`TIMESTAMP(p)`		7 + ↑`p` ÷ 2↑ + 7
14	Binary data	`BINARY`		`definition-length` + 7
15	Binary data	`VARBINARY`		`definition-length` + 9

Legend:: p: Number of digits in the fractional seconds (0, 3, 6, 9 or 12. The default value is 0.)

grouping_set_func_size(i)

Data length of each set function result

Determine the data length of each set function result as explained in Table 6‒8: Value to be substituted for grouping_col_data_size according to the data type of the set function result.

DECOMPRESS_WORK_SIZE

Decompression work area

Use the following formula to determine the value:

Formula (bytes)

[Figure]

col_data_size(i)

Data length of each column

To calculate the data length, see Table 6‒9: Data length of each data type.

TXT_WORK_SIZE

Work area for text indexes

Use the following formula to determine its value. If no text index is defined or used, assign 0.

Formula (bytes)

[Figure]

AUDITREAD_WORK_SIZE

Retrieval work area for the ADB_AUDITREAD function (bytes)

Use the following formula to determine its value. If the ADB_AUDITREAD function will not be used, assign 0.

AUDITREAD_WORK_SIZE = (AUDREADSZ + AUDBLOCKSZ) × 1,024

AUDREADSZ

Audit trail file management area

Substitute the following value. If the ADB_AUDITREAD function will not be used, assign 0.

Value (kilobytes)

AUDREADSZ = 1

AUDBLOCKSZ

Audit trail file work area

Substitute the following value. If the ADB_AUDITREAD function will not be used, assign 0.

Value (kilobytes)

AUDBLOCKSZ = 262,144

SCAN_INFO_SIZE: Table retrieval management area (bytes)

[Figure]

: Determine the value of the SCAN_INFO_DATA_SIZE variable for each retrieval-target table, and then add up the determined values.
: If the results derived from hash joins correspond to the outermost table of a nested loop join, calculate the value of the SCAN_INFO_HASH_SIZE variable for each hash join. If the results derived from hash execution of a subquery correspond to the outermost table of a nested loop join, calculate the value of the SCAN_INFO_HASH_SIZE variable for each hash execution of the subquery. Specify the total of the calculated values.

SCAN_INFO_DATA_SIZE: Table retrieval management area for base tables and joined tables (bytes)

SCAN_INFO_DATA_SIZE = tbl_access_num × (TBL_ACCESS_INFO_SIZE + OUTERJOIN_INFO_SIZE)

tbl_access_num: Number of table retrievals

Substitute whichever of the following values applies:

If the table is not joined: 1
If the table is joined by a nested loop join

- If the table is the outermost of the nested loop join: 1

- If the table is an inner table of the nested loop join: The number of rows in the outer table of that inner table
If the table is joined by a hash join

- If the table is the outer table of the hash join: 1

- If the table is the inner table of the hash join: 1

If the table is specified in a subquery that includes an external reference column, and the processing method of that subquery is nested loops work table execution or nested loops row value execution, specify the preceding value multiplied by uthd_num × sql_rthd_num as the number of table retrievals.

uthd_num: Value specified for the adb_sys_uthd_num operand in the server definition
sql_rthd_num: Value specified for the adb_sql_exe_max_rthd_num operand in the server definition

Example of calculating number of table retrievals

SELECT * FROM "T1" A,"T2" B,"T3" C
           WHERE A."C1"=B."C1" AND B."C2"=C."C2"

When executing this SELECT statement where tables T1, T2, and T3 are joined by a nested loop join in that order from the outside in, the table retrieval counts are as follows:

Retrieval count of table T1: 1
Retrieval count of table T2: The number of lines in the results obtained by searching table T1
Retrieval count of table T3: The number of lines in the results obtained by searching tables T1 and T2

TBL_ACCESS_INFO_SIZE: Table retrieval information area

Substitute whichever of the following values applies:

If the retrieval method of the target table is table scan: TBL_SCI_MEM
If the retrieval method of the target table is index scan using a B-tree index or key scan: IDX_SCI_MEM
If the retrieval method of the target table is index scan using a text index: IDX_SCI_TXT_MEM
If the retrieval method of the target table is work table scan: WRKTBL_SCI_MEM

TBL_SCI_MEM: The table retrieval information area during table scan (bytes)

TBL_SCI_MEM = (336 + RESULT_WORK_SIZE) × (dbarea_file_num + chunk_num + SGTBL - 2)

dbarea_file_num

Number of DB area files for the DB areas that store the table to be searched

chunk_num

Number of chunks in table to be searched (or 1 if the table to be searched is a single-chunk table)

SGTBL

Number of segments in table to be searched

For the formula for determining the value of SGTBL, see the following subsection in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the retrieval-target table.

Single-chunk table that is a row store table

(a) Determining the variable SGROWTBL (for a single-chunk table)
Single-chunk table that is a column store table

(b) Determining the variable SGCOLUMNTBL (for a single-chunk table)
Multi-chunk table that is a row store table

(f) Determining the variable SGROWTBL (for a multi-chunk table)
Multi-chunk table that is a column store table

(g) Determining the variable SGCOLUMNTBL (for a multi-chunk table)

IDX_SCI_MEM: Table retrieval information area for retrieval using a B-tree index (bytes)

IDX_SCI_MEM = IDX_CHUNK_SCI_MEM + IDX_SCAN_IDX_MEM

IDX_CHUNK_SCI_MEM

Table retrieval information area for chunks during retrieval using a B-tree index (bytes)

IDX_CHUNK_SCI_MEM = 368 + RESULT_WORK_SIZE

Note that IDX_CHUNK_SCI_MEM = 0 if either of the following conditions are met:

The table to be searched is a multi-chunk table consisting of one chunk
The table to be searched is a single-chunk table

IDX_SCAN_IDX_MEM

Table retrieval information area for all chunks during retrieval using a B-tree index (bytes)

[Figure]

Note that IDX_SCAN_IDX_MEM = 0 if the number of chunks to be searched is 0.

chunk_num

Number of chunks to be searched

IDX_SCAN_IDX_MEM(s)

Table retrieval information area for B-tree index of s-th chunk (bytes)

[Figure]

r

2 if there are two or more types of key values of rows that satisfy the search condition, and 0 if there is one type.

n

The minimum value of k where PIDX(k) = 1.

PIDX(k)

Determine this value by using Formula 1 (for determining PIDX(k)) in (2) Determining the number of storage pages used in the upper page segment (variable IP_UPPER(i)) under 5.8.3 Determining the number of storage pages for each B-tree index segment.

For the variables key_num, dup_key_num, and key_dup used in the formula for determining PIDX(k), determine the average number of key types and average number of duplicate keys for keys that satisfy the search conditions. When calculating the number of key types and duplicate keys, include invalid row data for rows that are deleted by a DELETE statement or updated by an UPDATE statement. If rows are being repeatedly added by INSERT statements or repeatedly modified by UPDATE statements, or there is variation in the number of duplicate keys or the key length, use a value that is 1.2 to 1.5 times the value determined by the formula.

PDUP

Determine this value by using Formula 2 (for determining PDUP) in (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)) under 5.8.3 Determining the number of storage pages for each B-tree index segment.

For the variables dup_key_num and dup_key_dup used in the formula for determining PDUP, determine the average number of key types and average number of duplicate keys for keys that satisfy the search conditions. When calculating the number of key types and duplicate keys, include invalid row data for rows that are deleted by a DELETE statement or updated by an UPDATE statement. If rows are being repeatedly added by INSERT statements or repeatedly modified by UPDATE statements, or there is variation in the number of duplicate keys or the key length, use a value that is 1.2 to 1.5 times the value determined by the formula.

PIDX_MEM

Table retrieval information area for upper or leaf page of a B-tree index (bytes)

[Figure]

idx_col_num

Number of indexed columns in a B-tree index

key_size(i)

Key length of i-th indexed column

Calculate key_size(i) as explained in 5.8.4 Determining the key length (KEYSZ) of a B-tree index.

Interpret the "Actual data length" in Table 5‒18: B-tree index key length as the defined length of the data.

PDUP_MEM

Table retrieval information area for low-ID directory pages or low-ID list page of a B-tree index (bytes)

[Figure]

idx_col_num

Number of indexed columns in a B-tree index

key_size(i)

Key length of i-th indexed column

Calculate key_size(i) as explained in 5.8.4 Determining the key length (KEYSZ) of a B-tree index.

Interpret the "Actual data length" in Table 5‒18: B-tree index key length as the defined length of the data.

IDX_SCI_TXT_MEM

Table retrieval information area for retrieval using a text index (bytes)

IDX_SCI_TXT_MEM = IDX_CHUNK_TXT_MEM + IDX_SCAN_TXT_MEM

IDX_CHUNK_TXT_MEM

Table retrieval information area for chunks during retrieval using a text index (bytes)

[Figure]

Note that IDX_CHUNK_TXT_MEM = 0 if either of the following conditions are met:

The table to be searched is a multi-chunk table consisting of one chunk
The table to be searched is a single-chunk table

scan_str_num

Number of characters in the search character string specified in the LIKE predicate, LIKE_REGEX predicate, or CONTAINS scalar function that uses a text index (specify 1,001 if the number exceeds 1,001)

TXT_SCAN_NODE_NUM

Number of nodes used for text index searches

TXT_SCAN_NODE_NUM = scan_nest_num + sortcode_str_num + TXT_SYNONYM_NUM

scan_nest_num

Number of nested OR conditions in the LIKE predicate, LIKE_REGEX predicate, or CONTAINS scalar function that uses a text index

sortcode_str_num

Number of characters in the search string when performing correction search in a CONTAINS scalar function

TXT_SYNONYM_NUM

Average number of synonyms in synonym groups when using synonym search in a CONTAINS scalar function

[Figure]

all_synonym_num

The number of synonyms registered in the synonym dictionary specified when performing a synonym search in a CONTAINS scalar function

This value is the total number of synonyms in all synonym groups. Specify 0 if you do not perform synonym searches.

synonym_group_num

The number of synonym groups registered in the synonym dictionary specified when performing a synonym search in a CONTAINS scalar function

IDX_SCAN_TXT_MEM

Table retrieval information area for all chunks during retrieval using a text index (bytes)

[Figure]

Note that IDX_SCAN_TXT_MEM = 0 if the number of chunks to be searched is 0.

chunk_num

Number of chunks to be searched

IDX_SCAN_TXT_MEM(s)

Table retrieval information area for text index of s-th chunk (bytes)

[Figure]

WRKTBL_SCI_MEM

Table retrieval information area for work table retrieval (bytes)

WRKTBL_SCI_MEM = (304 + RESULT_WORK_SIZE) × WRKTBL_PGNUM

WRKTBL_PGNUM

Number of pages of work table to be searched

Calculate WP(i) as explained in 5.9.2 Determining the number of pages for base rows that are needed for storing work tables.

SCAN_INFO_HASH_SIZE

Table retrieval management area when retrieval is performed using a hash table (bytes)

[Figure]

hashtbl_row_len

Hash table row length

Calculate the hash table row length as explained in 6.25.3 Number of work tables created during retrieval using hash tables.

hashtbl_row_num

Number of result rows derived from a hash join or hash execution of a subquery

If you cannot determine the number of rows, specify 1,044,480.

sql_rthd_num

The maximum number of SQL processing real threads (the value specified for the adb_sql_exe_max_rthd_num operand in the server definition).

OUTERJOIN_INFO_SIZE

Outer join management area (bytes)

OUTERJOIN_INFO_SIZE = 120 + 16 × sql_rthd_num + RESULT_WORK_SIZE

Note that OUTERJOIN_INFO_SIZE = 0 if all of the following conditions are met:

The table to be searched is not specified on the left side of the outer join
The table to be searched is not specified on the right side of the outer join

sql_rthd_num: The maximum number of SQL processing real threads (the value specified for the adb_sql_exe_max_rthd_num operand in the server definition).

BUF_DLYSZ

Area that is used to report the updated pages

Substitute the following value.

Value (kilobytes)

[Figure]

ARC_DIR_PATH

Area that is used to manage the path name of the archive directory to be deleted

Use the formula shown below to determine its value.

Formula (kilobytes)

[Figure]

archivedir_path_num

If you execute the TRUNCATE TABLE statement, substitute 1.

If you execute the PURGE CHUNK statement, substitute the number of chunks to be deleted.

RNGIDX_INFO_SIZE

Range index information area for chunks

Use the following formula to determine its value.

Formula (bytes)

[Figure]

rngidx_num

Total number of range indexes that are used for retrieval in tables that are searched by using B-tree indexes, among the range indexes that are used to skip chunks during execution of an SQL statement

If one or more SQL statements are executed more than once for the same range index, assume the total number to be 1. If the total number is 0, substitute 0 for RNGIDX_INFO_SIZE.

Note that you can determine this value from the display result of the access path information by using the following procedure.

In the display result details view of the access path information, check Information related to table retrieval methods and Information related to indexes. Find all items that meet either of the following conditions:
- The table retrieval method is INDEX SCAN, and INDEX TYPE of the first index information in Information related to indexes is B-TREE (B-tree index).
- The table retrieval method is KEY SCAN.
Find all items that meet the condition in step 1 and for which Information related to indexes meets both of the following conditions:
- INDEX TYPE is RANGE (range index).
- SKIP COND is CHUNK.
Determine the total number of INDEX NAME values in the Information related to indexes that meets the conditions in step 2, eliminating duplication in names. Use the determined value for rngidx_num.

In the following example of the display result of access path information, there are two INDEX NAME values: T1RIX1 (appearing twice) and T2RIX1 (appearing once). Therefore, rngidx_num is 2.

[Figure]

For details about access paths, see How to use access paths (how to use SQL statement execution plans) in the HADB Application Development Guide.

chunk_num(i)

Number of chunks created for the table in which the i-th range index is defined

Exclude the number of archive chunks. In the case of a single-chunk table, the value of this variable is 1.

RNGIDX_INFO_ENTRY_SIZE(i)

Range index information entry size

Use the following formula to determine its value.

Formula (bytes)

[Figure]

col_size(i)

Data length of a column for which a range index is defined (bytes)

For details about the data length of each column, see Table 6‒9: Data length of each data type.

Determine the data length from the following table.

Table 6‒9: Data length of each data type
No.	Classification	Data type		Data length (bytes)
1	Numeric data	`INTEGER`		8
2		`SMALLINT`		4
3		`DECIMAL`	1 ≤ `precision` ≤ 4	2
4			5 ≤ `precision` ≤ 8	4
5			9 ≤ `precision` ≤ 16	8
6			17 ≤ `precision` ≤ 38	16
7		`DOUBLE` `PRECISION`		8
8	Character string data	`CHAR`		`definition-length`
9		`VARCHAR`	1 ≤ `definition-length` ≤ 32,000	`definition-length` + 2
10		`VARCHAR`	32,001 ≤ `definition-length` ≤ 64,000	`definition-length` + 4
11	Datetime data	`DATE`		4
12		`TIME(p)`		3 + ↑`p` ÷ 2↑
13		`TIMESTAMP(p)`		7 + ↑`p` ÷ 2↑
14	Binary data	`BINARY`		`definition-length`
15	Binary data	`VARBINARY`		`definition-length` + 2

Legend:: p: Number of digits in the fractional seconds (0, 3, 6, 9 or 12. The default value is 0.)
Note: For calculating CURRENT_USER, assume the data type is VARCHAR and the defined length is 100.

(d) Determining the variable RTHD_ROLLBKSZ

The variable RTHD_ROLLBKSZ is required when rollback processing is executed or when the HADB server is restarted. Substitute the following value.

Value (kilobytes): RTHD_ROLLBKSZ = 67

(e) Determining the variable RTHD_DBEXTSZ

The variable RTHD_DBEXTSZ is required when a DB area is extended. Substitute the following value.

Value (kilobytes): RTHD_DBEXTSZ = 32,768

(f) Determining the variable RTHD_WORKBUF

The variable RTHD_WORKBUF is required when a work table is used. Use the following formula to determine its value.

Formula (kilobytes)

RTHD_WORKBUF =
↑(clt_wrk_page_num × (wrk_page_size + 304) +8
× (clt_wrk_page_num + WIO_NUM) + wrk_page_size) ÷ 1,024↑ × 1.05

Explanation of variables

clt_wrk_page_num

Value specified for the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition (Value specified for the export option adb_export_wrktbl_blk_num when the adbexport command is to be executed)
wrk_page_size

Determine this value in bytes based on the explanation of the page size of the work table DB area in Table 6‒3: DB area page size under (2) Determining the global buffer page requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.
WIO_NUM

Substitute the value specified for the adb_dbbuff_wrktbl_clt_blk_num client definition operand (or the value specified for the export option adb_export_wrktbl_blk_num when the adbexport command is to be executed), or the value of the variable UTHNUM, whichever is smaller. For details about the variable UTHNUM, see (a) Determining the variable SCI in (3) Determining the process common memory requirement (for starting the HADB server) under 6.3.3 Determining the memory requirement for starting the HADB server.

(g) Determining the variable RTHD_EXESQLDICSZ

The variable RTHD_EXESQLDICSZ is required when a data manipulation SQL statement is preprocessed. Use the following formula to determine its value.

Note that if the target table is a viewed table, add 39 kilobytes to the value determined by the formula.

Formula (kilobytes)

[Figure]

Explanation of variables

LIKE_REG_PRD_SIZE

Execution management area for the LIKE_REGEX predicate (bytes)

Use the formula shown below to determine its value.

Note that if the LIKE_REGEX predicate is not specified, substitute 0 for the variable LIKE_REG_PRD_SIZE.

[Figure]

scan_str_num

Number of characters in the character string specified as the pattern character string

normal_factor_size

Number of regular factors in the regular expression specified as the pattern character string

paren_nest_num

Number of parentheses in the regular expression specified as the pattern character string

vertical_line_num

Number of vertical bars in the regular expression specified as the pattern character string

quantifier_nest_num

Number of quantifiers in the regular expression specified as the pattern character string

repetition_factor_sum

Total of the upper limits specified for the repetition factors in the regular expression specified as the pattern character string

If no upper limits are specified for repetition factors, substitute the lower limit value specified for the repetition factors.

Regular expression specification example

((a{2}){5}){8,10}

Formula (count)

[Figure]

The following shows examples of regular expressions and formulas for determining the variable LIKE_REG_PRD_SIZE.

Regular expression specification example 1

KFAA11[0-9]{3}-E

Formula example (bytes)

[Figure]

Regular expression specification example 2

KFAA6000[2-9]-E.*uid=12345.*

Formula example (bytes)

[Figure]

BYTE_CODE_SIZE

Area that is used to store the bytecodes resulting from converting (for evaluation) the regular expression specified as the pattern character string in the execution management area for the LIKE_REGEX predicate (bytes)

Use the formula shown below to determine its value.

[Figure]

char_specifier_num

Total number of character specifiers that are used in the regular expression specified as the pattern character string

If repetition factors are specified for the character specifiers, add the number of repetitions to the value of this variable. If there is a repetition factor in a nested form (such as (a{10}){10}), multiply each number of repetitions. Then, add the result of multiplication to the variable char_specifier_num.

For details about the number of repetitions specified for repetition factors, see Table 6‒7: Determining the number of repetitions specified for a repetition factor in (c) Determining the variable RTHD_EXESQLSZ.

quantifier_num

Number of quantifiers excluding the repetition factors in the regular expression specified as the pattern character string

char_class_num

Total number of character classes in the regular expression specified as the pattern character string

If repetition factors are specified for the character classes, add the number of repetitions to the value of this variable. If there is a repetition factor in a nested form (such as (\d{10}){10}), multiply each number of repetitions. Then, add the result of multiplication to the variable char_class_num.

normal_char_set_num

Total number of regular character set identifiers in the regular expression specified as the pattern character string

If repetition factors are specified for the regular character set identifiers, add the number of repetitions to the value of this variable. If there is a repetition factor in a nested form (such as ([[:alpha:]]{10}){10}), multiply each number of repetitions. Then, add the result of multiplication to the variable normal_char_set_num.

Note: In the case of ([[:alpha:]]{10}){10}, a regular character set is also repeated. Therefore, you must also add 100 to the variable char_list_num.

char_list_num

Total number of regular character sets in the regular expression specified as the pattern character string

If repetition factors are specified for the regular character sets, add the number of repetitions to the value of this variable. If there is a repetition factor in a nested form (such as ([abc]{10}){10}), multiply each number of repetitions. Then, add the result of multiplication to the variable char_list_num.

repetition_factor_sum

Total of the upper limits specified for the repetition factors in the regular expression specified as the pattern character string

If no upper limits are specified for repetition factors, substitute the lower limit value specified for the repetition factors.

Regular expression specification example

((a{2}){5}){8,10}

Formula (count)

[Figure]

The following shows examples of regular expressions and formulas for determining the variable BYTE_CODE_SIZE.

Regular expression specification example 1

KFAA11[0-9]{3}-E

Formula example (bytes)

[Figure]

Regular expression specification example 2

KFAA6000[2-9]-E.*uid=12345.*

Formula example (bytes)

[Figure]

CONTAINS_PRD_SIZE

Execution management area for the scalar function CONTAINS (bytes)

Use the following formula to determine this value.

Note that if the scalar function CONTAINS is not specified, substitute 0 for the variable CONTAINS_PRD_SIZE.

[Figure]

scan_str_num

Number of characters in the character string specified as the pattern character string

SYN_LIST_SIZE

Area that is used to store synonyms (bytes)

Use the following formula to determine this value.

Note that if no synonym-search specification is included in the scalar function CONTAINS, substitute 0 for the variable SYN_LIST_SIZE.

[Figure]

syn_str_num

Average number of synonyms

syn_group_size

Average number of synonyms specified in one synonym group

(h) Determining the variable RTHD_COMMUSZ

Add the variable RTHD_COMMUSZ when processing communication. Use the following formula to determine its value.

Formula (kilobytes)

RTHD_COMMUSZ = RCVBUF + SNDBUF

Explanation of variables

RCVBUF: Receive buffer setting

Use the following formula to determine its value.

Formula (kilobytes)

RCVBUF = ↑(352 + CMSG) ÷ 1,024↑

CMSG: Size of data sent from HADB clients

See the variable CMSG under Memory required for communication between an HADB client and the HADB server in Estimating the Memory Requirements for an HADB Client in the HADB Application Development Guide.

SNDBUF: Send buffer setting

For the send buffer size during the initial allocation, substitute the following value.

Value (kilobytes)

SNDBUF = 4

If send data exceeding the initially allocated send buffer size is created, the HADB server reallocates the value determined from the following formula.

Formula (kilobytes)

SNDBUF = ↑SMSG ÷ 4,096↑ × 4

SMSG: Size of data sent by the HADB server

The size of send data depends on the processing content. The following table shows the size of the send data allocated in various processes.

Table 6‒10: Size of send data allocated in various processes
No.	Processing	Formula for determining the send data size (bytes)
1	SQL statement is executed.
2	SQL statement execution is `NOROW`.
3	`FETCH`
4	`FETCH` is `NOROW` (one or more rows were fetched in a batch transfer).
5	SQL statement is preprocessed.
6	The `adbdbstatus` command is executed

Legend:

srv_base_info: Send data base information

Substitute 152 bytes.

srv_execute: Execution-specific information

Substitute 24 bytes.

srv_fetch: FETCH-specific information

Substitute 16 bytes.

srv_norow: NOROW-specific information

Substitute 520 bytes.

srv_prepare: Preprocessing-specific information

Substitute 56 bytes.

srv_status_info: Information specific to DB status analysis

Substitute 1,048,576 bytes.

ARRAY_RESULT: Batch updating result

Use the following formula to determine its value.

Formula (bytes)

ARRAY_RESULT = (array_num - 1) × 8

array_num: Number of dynamic parameter pairs that were batch-updated

This value depends on the application installation method.

Application that uses CLI functions

The value of this variable is the number of ArrayCount arguments specified for a_rdb_SQLBindArrayParams(). If a_rdb_SQLBindParams() is used to join parameters, the value if this variable is fixed to 1.
Application that uses the JDBC driver

The value of this variable is the number of parameter lists that are registered by using the addBatch method. If the executeBatch or executeLargeBatch method is not used, the value of this variable is fixed to 1.

FETCH_DATA: Batch transfer row data

Use the following formula to determine its value.

Formula (bytes)

[Figure]

col_num

Number of selection expressions specified in the SQL statement

col_size(i)

Data length of a selection expression

fetch_size: Number of rows sent in a batch during FETCH processing

Substitute the value specified for the adb_clt_fetch_size operand in the client definition. For details about the adb_clt_fetch_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

PREPARE_INFO_SIZE: SQL statement preprocessing information

See the description of the variable PREPARE_INFO_SIZE in (c) Determining the variable RTHD_EXESQLSZ.

APATHVIEW: Access path information control area that is created for displaying an access path

See the description of the variable APATHVIEW in (b) Determining the variable PROC_TOTALSQLSSZ under (1) Determining the process common memory requirement (during normal operation).

(i) Determining the variable RTHD_EXPSQLTRCSZ

Add the RTHD_EXPSQLTRCSZ variable when the SQL trace buffer is extended (the SQL trace buffer is extended when the value of the RTHD_EXPSQLTRCSZ variable exceeds 32,768 kilobytes). Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variables

PARAM_INFO_SIZE: See the description of the variable PARAM_INFO_SIZE in (c) Determining the variable RTHD_EXESQLSZ.
array_num: Number of dynamic parameter sets specified during batch update processing

(j) Determining the variable RTHD_AUDINFSZ

Add the variable RTHD_AUDINFSZ when the audit trail facility is enabled. Substitute the following value.

Value (kilobytes)

RTHD_AUDINFSZ = 3

(k) Determining the variable RTHD_COLUMNIZESZ

Add the variable RTHD_COLUMNIZESZ when the updated-row columnizing facility is enabled. Use the following formula to determine this value.

Formula (kilobytes)

[Figure]

Explanation of variables

TABLELIST: Table name list information

Substitute the following value.

Value (kilobytes)

TABLELIST = 833

table_num: Total number of column store tables to which the updated-row columnizing facility is applied

For the column store tables to which the updated-row columnizing facility is applied, see (1) Checking the column store tables to which the facility is applied in 11.18.2 Preparation tasks.

TABLEINFO(i): Information about the i-th column store table to which the updated-row columnizing facility is applied

Use the following formula to determine this value.

Formula (kilobytes)

[Figure]

column_num: Number of columns in the i-th column store table to which the updated-row columnizing facility is applied

index_num: Number of range indexes defined for the i-th column store table to which the updated-row columnizing facility is applied

chunk_num

Number of chunks in the i-th column store table to which the updated-row columnizing facilityis applied

For a single-chunk table, substitute 1.

SEGMENTLIST(k): Segment list information for the k-th chunk

Use the following formula to determine this value.

Formula (kilobytes)

SEGMENTLIST(k) = ↑24 × segment_num ÷ 1,024↑

segment_num

Number of row-data segments for the k-th chunk

Determine the value by referring to the explanation of the following variable in (2) Explanation of variables in 5.8.1 Determining the total number of pages in the data DB area.

■ For a multi-chunk table

Variable ROWDATASEGNUM(i,k) in (g) Determining the variable SGCOLUMNTBL (for a multi-chunk table)

■ For a single-chunk table

Variable ROWDATASEGNUM(i) in (b) Determining the variable SGCOLUMNTBL (for a single-chunk table)

RELOCATE_DAT(i): Work area for re-allocating the i-th column store table to which the updated-row columnizing facility is applied

Use the following formula to determine this value.

Formula (kilobytes)

RELOCATE_DAT(i) =
  ↑(max_rowsz × 2 + 1,768 + 16 × col_num
       + ↓(96 + 216^# + 544 + (652 + page_size) × col_num
             + (80 + page_size) × SEGSIZE + 7) ÷ 8↓ × 8
      + 432 + 128 × col_num
      + ↓(2 × col_num + 7) ÷ 8↓ × 8
      + (1,736 + 16 × col_num) × col_num + max_rowsz
     ) ÷ 1,024↑
  + 16,384 + 1,024 × col_num

#: Add this value if range indexes are defined for the i-th column store table to which the updated-row columnizing facility is applied.

max_rowsz

Maximum row length (bytes)

Determine the maximum row length based on the formula for the row length ROWSZ in (1) Determining the number of pages for base rows (variable BP(i)) under 5.8.2 Determining the number of pages for storing each type of row.

page_size

Page size of the data DB area that stores the i-th column store table to which the updated-row columnizing facility is applied (bytes)

Determine this value by referring to Table 6‒3: DB area page size in (2) Determining the global buffer page requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

col_num: Number of columns in the i-th column store table to which the updated-row columnizing facility is applied

SEGSIZE

Segment size of the data DB area that stores the i-th column store table to which the updated-row columnizing facility is applied (pages)

Use the following formula to determine this value.

SEGSIZE = 4,194,304 ÷ page_size

RTHD_DATACOMPRESS(i): Work area for compressing the data of the i-th column store table to which the updated-row columnizing facility is applied

Determine the value by referring to the explanation of the variable RTHD_DATACOMPRESS in (d) Determining the variable RTHD_DATALOAD in (2) Determining the real thread private memory requirement (for executing the adbimport command) in 6.3.6 Determining the memory requirement for executing the adbimport command.

To Page Top

(3) Determining the heap memory requirement (during normal operation)

Use the following formula to determine the heap memory requirements (HEAP_EXECSZ) during normal operation.

Formula (kilobytes)

HEAP_EXECSZ = max_sql( UTHD_WORKSZ ) x rthd_num

Explanation of variables

rthd_num

Value specified for the adb_sys_rthd_num operand in the server definition

max_sql( )

Calculate the result for the variable in the parentheses for each SQL statement to be executed. Then, substitute the largest among the determined values.

UTHD_WORKSZ: Pseudo-thread work area

Determine this value from the following formula. Note, however, that if the following condition is met, substitute 0:

The SQL statement to be executed is not a SELECT statement

Formula (kilobytes)

UTHD_WORKSZ = ↑QE x uthd_num ÷ 2↑

uthd_num: Value specified for the adb_sys_uthd_num operand in the server definition

QE: Value determined by applying the rules for the variable QB to the query expression body of the query expression

QB

How you determine the value of this variable depends on whether the query expression body includes set operators.

■ If the query expression body does not include set operators

Value determined by applying the rules for the variable QS to the query specification of the query expression body

■ If the query expression body includes set operators

Use the following formula to determine the value:

QB = MA(ST1, ... , STi) + setop_num

ST1, ... , STi: Value determined by applying the rules for the variable QS to each query specification in a set operand

setop_num: Number of set operations specified in the query expression body

QS

How you determine the value of this variable depends on whether the query expression body includes subqueries.

■ If the query specification does not include subqueries

Use the following formula to determine the value:

QS = scan_num + 1

■ If the query specification includes subqueries

Use the following formula to determine the value:

QS = MAX(SQ1, ... , SQi) + scan_num + 1

SQ1, ... , SQi: Value determined by applying the rules for the variable QB to each subquery specified in the query specification

scan_num

Total number of the following elements specified in the query specification

GROUP BY clauses
SELECT DISTINCT
DISTINCT set functions
Inverse distribution functions
Window functions
Number of derived tables
Number of joined tables (count each table as 3 in the case of FULL OUTER JOIN)
number-of-tables-that-are-not-joined-tables - 1
number-of-archivable-multi-chunk-tables x 3

The following shows an example of determining the QE variable:

■ Example of determining the variable QE

[Figure]

Note the following when specifying viewed tables and query names:

■ Notes about specifying viewed tables and query names

When a viewed table or query name is specified in the SQL statement to be executed, estimate the memory requirements as if an internal derived table corresponding to each viewed table or query name were applied. An example follows.

Example: View definition

CREATE VIEW "V1" AS SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C2"

Example: SQL statement to be executed

SELECT * FROM "V1","T3" WHERE "V1"."C1"="T3"."C3"

With this particular combination of view definition and SQL statement to be executed, the SQL statement for which memory requirements are to be estimated is as follows:

SQL statement for which memory requirements are to be estimated

SELECT * FROM (SELECT * FROM "T1","T2" WHERE "T1"."C1"="T2"."C2"),"T3"
         WHERE "V1"."C1"="T3"."C3"

To Page Top