Table 16-2 lists the optimization information that can be collected by the optimizing information collection utility or that can be registered using the optimization information parameter file.
Table 16-2 Optimization information collected by the optimizing information collection utility or registered using the optimization information parameter file
No. | Information collected | Classification | Description | SQL statement that improves precision of optimization | Processing that has adverse effects on integrity between registered optimization information and database status | Item name* |
---|---|---|---|---|---|---|
1 | Number of rows | T | Total number of rows in the target table | SQL statement containing join | SQL statement that involves an updating operation and that changes the number of rows in the target table | NROWS |
2 | Number of data pages | T | Total number of row data storage pages in the target table | SQL statement that contains columns comprising the index in the search condition | SQL statement that involves an updating operation | NPAGES |
3 | Number of unique index key values | I | Null value (when the optimization information parameter file is used, the specified value is registered) | NENTRY | ||
4 | Number of index pages | I | Total number of index storage pages used | NIPAGES | ||
5 | Number of index levels | I | Maximum number of index levels in each single server or back-end server | NLEVEL | ||
6 | Sequentialness | I | Degree of sequentialness of the row data stored in order of index key values | SEQ_RATIO | ||
7 | Number of null values | C | Number of null values in the columns | SQL statement that contains a column comprising a single-column index or the first column comprising a multicolumn index | NNULLS | |
8 | Maximum number of duplicate column values | C | Maximum number of duplicate values for a column | N_MAX_DUP_KEY | ||
9 | Minimum number of duplicate column values | C | Minimum number of duplicate values for a column | N_MIN_DUP_KEY | ||
10 | Column value distribution information | C | Column value distribution information | MAX_VALUE, MIN_VALUE | ||
11 | Number of unique column values | C | Number of unique column values | NUNIQUE | ||
12 | Statistical information cache size | T | Work area used by the HiRDB system | ![]() | ![]() | ![]() |
Table 16-3 shows the information that is collected or registered as key value distribution information for index component columns.
Table 16-3 Information collected or registered as key value distribution information for index component columns
No. | Information collected | Description |
---|---|---|
1 | Number of null values | All rows in the target table |
2 | Number of table rows | Total number of pages storing the row data of the target table |
3 | Column data length | Data length of the column |
4 | Column data type | Data type of the column |
5 | Number of sections |
|
6 | Maximum column value | Maximum value of the column |
7 | Minimum column value | Minimum value of the column |
8 | Degree of cumulation between sections | Total number of elements from section 1 through the corresponding section |
9 | Number of unique values in section | Number of unique column values in the corresponding section |
10 | Maximum value in section | Maximum column value in the corresponding section |