18.2.1 Size of a work table file used by an SQL statement

To determine the size of a work table file used by an SQL statement, use the following formula:

Size of a work table file used by an SQL statement (bytes) = MAX (a, b) x c
a: Maximum size of a work table file that is used by one SQL statement
Calculate the size of a work table file for each SQL statement and use the largest such size as the value of a. For details about how to obtain this value, see (1) Formula for calculating the size of the work table file to be used by one SQL as follows.
b: Maximum size of a work table file that is used by an ASSIGN LIST statement.
Calculate the size of a work table file for each ASSIGN LIST statement and use the largest such size as the value of b. For details about how to obtain this value, see (2) Formula for calculating the size of the work table file to be used by the ASSIGN LIST statement as follows.
c: Value of the pd_max_users operand
However, when multiple front-end servers are being used, the back-end servers are value of the pd_max_bes_process operand.
Organization of this subsection
(1) Formula for calculating the size of the work table file to be used by one SQL
(2) Formula for calculating the size of the work table file to be used by the ASSIGN LIST statement

(1) Formula for calculating the size of the work table file to be used by one SQL

The following formula is used to calculate the size of the work table file that is to be used by one SQL statement.

Formula
Size of work table file to be used by one SQL statement (bytes) = a x b + c x d
a: Size of a column information work table
b: Maximum number of column information work tables
c: Size of a location information work table
d: Maximum number of location information work tables
(a) Obtaining the size of a column information work table

To obtain the size of a column information work table, use the following formula:

Formula
Size of a column information work table (bytes)
= [Figure] a[Figure] MIN{[Figure](b - 48) [Figure] c[Figure], 255 }[Figure] x b x 2
a: Number of rows in the column information work table (see Table 18-2)
b: Page length of the work table (use Formula 1 as follows)
c: Row length of the work table (use Formula 2 as follows)
Formula 1
Page length for a work table# = MAX {[Figure](row length for work table + 48) [Figure] 2,048[Figure] x 2,048, 4,096}
#: The page length of a work table must be no greater than 32,768 bytes.
Formula 2
Row length for a work table# =
n
[Figure] Ai + 2 x n + 6
i = 1
Ai:
Data length for each column in work table (see Table 18-3 for the calculation procedure)
n:
Number of columns in work table (see Table 18-3 for the calculation procedure)
#: The row length of a work table must be no greater than 32,720 bytes.
If the LIMIT clause is specified and the value of (number of offset rows + number of rows specified in the LIMIT clause) is 32,768 or greater, add 12 to the row length of the work table obtained from Formula 2. However, addition of 12 is not necessary in the following cases:
  • The table to be searched is locked in the EX mode.
  • WITHOUT LOCK was specified.
  • The rapid grouping facility was specified.
  • Multiple tables are to be joined.

    Table 18-2 Obtaining the number of rows in a column information work table

    SQL statementNumber of rows in column information work table
    SELECT statementThis is the total number of rows subject to retrieval in individual tables. If multiple tables are joined, then use the resulting number of rows, if it is greater.
    CREATE INDEX statementThis is the number of rows in a table. For an index for repetition columns, use the total number of elements per repetition column among the index component columns.

    Table 18-3 Obtaining the data length for each column and the number of columns in a work table

    SQL statementNAi
    SELECT statementNumber of columns specified in the select expression + number of columns specified in the GROUP BY clause# + number of columns specified in the ORDER BY clause# + number of column specified in the HAVING clause# + 1 if the FOR UPDATE clause is specified#
    If ROW is specified in the selection expression, specify the total number of rows in the table.
    Data length for each column
    However, for large object data (BLOB), character data with a defined length of 256 or greater (including National and mixed character data), or binary data for columns that do not have the following attributes or for location information columns: 12
    • Column specified in a join condition (join column)
    • Selection expression with DISTINCT clause specified
    • Column specified in the subquery selection expression with a quantified predicate
    • Column specified in the subquery selection expression with the IN predicate
    • Selection expression in a query specification subject to set operation with UNION[ALL] or EXCEPT[ALL]
    • Column specified in the ORDER BY clause
    CREATE INDEX statement1 (index information column) + 1 (positional information column)
    • For an index information column, specify the sum of the data lengths for index component columns
    • 12 for a positional information column
Note
For details about the data lengths of columns, see the following tables:
#
When the columns are the same as ones specified in the selection expression, there is no need to add this term.
(b) Obtaining the maximum number of column information work tables

The following table shows how to calculate the maximum number of column information work tables.

Table 18-4 Obtaining the maximum number of column information work tables

SQL statementMaximum number of work tables for storing column information#1
SELECT statementWhen none of 1-10 as follows is applicable: 0
When any of 1-10 as follows is applicable: Sum of all the applicable values from 1-10
  1. When multiple tables are joined for retrieval
    Number of additional work tables (HiRDB/Single Server) = number of joined tables + 1
    Number of additional work tables (HiRDB/Parallel Server) = number of joined tables x 2
    If the join key column has an index and there is a limitation condition, the number of work tables is 0.
  2. When specifying the ORDER BY clause
    Number of additional work tables = 2
    When an index containing all the columns specified in the ORDER BY clause is to be used for search processing = 0
  3. When specifying a value expression containing a set function in the selection expression without specifying the GROUP BY clause#2
    Number of additional work tables = 1
  4. When specifying the GROUP BY clause
    Number of additional work tables = number of GROUP BY clauses specified x 2
  5. When specifying the DISTINCT clause
    Number of additional work tables = number of DISTINCT clauses specified x 2
  6. When specifying the UNION[ALL] or EXCEPT[ALL] clause
    Number of additional work tables (HiRDB/Single Server) = number of UNION[ALL] or EXCEPT[ALL] clauses specified + 2
    Number of additional work tables (HiRDB/Parallel Server) = (number of UNION[ALL] or EXCEPT[ALL] clauses specified + 1) x 2
  7. When specifying the FOR UPDATE clause or when using this cursor for updating purposes and specifying a search condition for a column with the index defined#2
    Number of additional work tables = 2
  8. When specifying the FOR READ ONLY clause
    Number of additional work tables = 1
  9. When specifying a subquery (quantified predicate)
    Number of additional work tables = number of subqueries specified + (number of =ANY quantified predicates for a column with the index defined) + (number of IN predicate subqueries specified for a column with the index defined) + (number of =SOME quantified predicates for a column with the index defined)
  10. When specifying the window function COUNT(*) OVER() in a selection expression
    Number of increased work tables = number of query specifications in which the window function is specified in the selection expression
CREATE INDEX statement2
#1: A work table might not be created depending on the access cost determined by HiRDB.
#2: Applicable only to a HiRDB/Parallel Server
(c) Obtaining the size of a location information work table

To obtain the size of a location information work table, use the following formula:

Formula
Size of a location information work table (bytes)
= [Figure] a[Figure] 184#[Figure] x 4,096 x 2
#: If an index-type plug-in function is specified as the search condition, use the value 155.
a: Number of rows in the location information work table
The following shows the procedure for obtaining the number of rows in the location information work table:
SQL statementObtaining the number of rows in the location information work table
SELECT statement
UPDATE statement
DELETE statement
If the search condition contains one predicate that includes a column with the index defined, use the number of rows for which the predicate is true. If there is more than one predicate, use the sum of the following values:
  • If OR operation is conducted on the predicates, the total number of rows for which at least one predicate is true.
  • If AND operation is conducted on the predicates, sum of the larger numbers of rows for which the predicates are true.
(d) Obtaining the maximum number of location information work tables

The following table shows how to calculate the maximum number of location information work tables.

Table 18-5 Obtaining the maximum number of location information work tables

SQL statementMaximum number of location information work tables
SELECT statementNumber of indexes to be used during search + 1 in either of the following cases:
  1. Search condition is specified for multiple columns with index defined.
  2. The FOR UPDATE clause is specified or this cursor is used for updating purposes and a search condition is specified for the column with index defined.#
  3. Search condition is specified for a column for which a repetition column index is defined.
  4. Facility for batch acquisition from functions provided by plug-ins is specified as the SQL optimization option and a function provided by a plug-in that uses a plug-in index is specified as a search condition.
In all these cases, the value is the number of indexes used during a search + 1.
UPDATE statement
DELETE statement
If the search condition contains a column with an index defined, use the number of indexes used during search processing + 1.
#: This is applicable to a HiRDB/Single Server only.

(2) Formula for calculating the size of the work table file to be used by the ASSIGN LIST statement

To obtain the size of a work table file used by the ASSIGN LIST statement, use the following formula:

Formula
Size of work table file to be used by the ASSIGN LIST statement (bytes) =
n
[Figure] (Bi x 2)
i = 1
n: Number of predicates in the selection condition of the ASSIGN LIST statement
Bi: Size of the work table used to process predicate i in the search condition. Use the following formula to obtain this value:
Bi = [Figure]number of rows for which predicate i is true in the base table for the list#[Figure] 504[Figure] x 4,096 x 1.5 (bytes)
#: If the predicate is a condition for a repetition column, this value is the total number of elements that are true.