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 |
The following formula is used to calculate the size of the work table file that is to be used by one SQL statement.
Size of work table file to be used by one SQL statement (bytes) = a x b + c x d |
To obtain the size of a column information work table, use the following formula:
Size of a column information work table (bytes) = ![]() ![]() ![]() ![]() ![]() ![]() |
Page length for a work table# = MAX {![]() ![]() ![]() |
Row length for a work table# = n ![]() i = 1 |
Table 18-2 Obtaining the number of rows in a column information work table
SQL statement | Number of rows in column information work table |
---|---|
SELECT statement | This 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 statement | This 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 statement | N | Ai |
---|---|---|
SELECT statement | Number 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
|
CREATE INDEX statement | 1 (index information column) + 1 (positional information column) |
|
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 statement | Maximum number of work tables for storing column information#1 |
---|---|
SELECT statement | When 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
|
CREATE INDEX statement | 2 |
To obtain the size of a location information work table, use the following formula:
Size of a location information work table (bytes) = ![]() ![]() ![]() |
SQL statement | Obtaining 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:
|
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 statement | Maximum number of location information work tables |
---|---|
SELECT statement | Number of indexes to be used during search + 1 in either of the following cases:
|
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. |
To obtain the size of a work table file used by the ASSIGN LIST statement, use the following formula:
Size of work table file to be used by the ASSIGN LIST statement (bytes) = n ![]() i = 1 |