The table below 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 |
---|---|---|---|---|---|---|
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#2 | MAX_VALUE, MIN_VALUE, SECTION_COUNT, SEC_TOTAL_COUNT, SEC_UNIQUE, SEC_MAX_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 | -- | -- | -- |
The table below 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 column value in section | Maximum column value in the corresponding section# |
No. | Column's data type | Initial value | Data to be stored |
---|---|---|---|
1 | INT[EGER] | 0 | Table data# or initial value |
2 | SMALLINT | 0 | |
3 | [LARGE] DEC[IMAL] | +0 | |
4 | FLOAT or DOUBLE PRECISION | 0 | |
5 | SMALLFLT or REAL | 0 | |
6 | CHAR[ACTER] |
|
|
7 | VARCHAR | Character string with a length of 0 |
|
8 | NCHAR |
|
|
9 | NVARCHAR | Character string with a length of 0 |
|
10 | MCHAR |
|
|
11 | MVARCHAR | Character string with a length of 0 |
|
12 | DATE | 0000-00-00 | Table data# or initial value |
13 | TIME | 00:00:00 | |
14 | INTERVAL YEAR TO DAY | +00000000. | |
15 | INTERVAL HOUR TO SECOND | +000000. | |
16 | TIMESTAMP | 0000-00-00 00:00:00.0....0 | |
17 | BINARY | Data with a length of 0 |
|