Optimization information to be collected

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	--	--	--

T: Table information.

I: Index information.

C: Column information:

When collecting optimization information by retrieval
This is the information on columns comprising an index; it is collected when an index is defined. In the case of a multicolumn index, only the information on the first column is collected.
When registering optimization information from the optimization information parameter file
This is the information on the columns specified in the optimization information parameter file.

#1: This item name is specified in the optimization information parameter file. For details about the item names that are specified in the optimization information parameter file, see 16.4.1 Items in the optimization information parameter file.

#2: If the column contains character string data, national character data, or mixed character string data, the data is enclosed in double quotation marks ("). If the data contains a control character or a character set is specified for the column, the double quotation marks are not displayed. Binary data is displayed in hexadecimal.; If the data type of the column is CHAR, NCHAR, or MCHAR, the first 16 bytes are displayed; if it is VARCHAR, NVARCHAR, or MVARCHAR, the first 14 bytes are displayed; if it is BINARY, the first 12 bytes are displayed. If multibyte characters are MCHAR or MVARCHAR and the length is at least 16 bytes or at least 14 bytes, respectively, the multibyte characters are displayed as is.; The hexadecimal representation is in the format x'****' or X'****', where **** indicates hexadecimal characters (0 to 9, a to f, A to F) in units of two characters.

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	Number of sections when the value of (number of table rows - number of rows whose key column data is null) is divided by a maximum of 100 If the number of rows is less than 100, the number of rows is set. Information in Nos. 8 to 10 is collected as many times as there are 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^#

#

When the table contains no data or all null values
The initial value shown in the table below is collected or registered.

When the table contains data other than the null value
The data to be stored, shown in the table below, is collected or registered.

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]	When the definition length is 16 bytes or less As many 0s as the definition length are collected or registered. When the definition length is 17 bytes or more 16 bytes of 0s are collected or registered.	When the definition length is 16 bytes or less Table data^# or initial value When the definition length is 17 bytes or more First 16 bytes of table data^# or the initial value
7	VARCHAR	Character string with a length of 0	When the length of the character string or data is 16 bytes or less Table data^# or initial value When the length of the character string or data is 17 bytes or more First 16 bytes of table data^# or the initial value
8	NCHAR	When the definition length is 8 bytes or less As many 0s as the definition length 2 are collected or registered. When the definition length is 9 bytes or more 16 bytes of 0s are collected or registered.	When the definition length is 16 bytes or less Table data^# or initial value When the definition length is 17 bytes or more First 16 bytes of table data^# or the initial value
9	NVARCHAR	Character string with a length of 0	When the length of the character string or data is 16 bytes or less Table data^# or initial value When the length of the character string or data is 17 bytes or more First 16 bytes of table data^# or the initial value
10	MCHAR	When the definition length is 16 bytes or less As many 0s as the definition length are collected or registered. When the definition length is 17 bytes or more 16 bytes of 0s are collected or registered.	When the definition length is 16 bytes or less Table data^# or initial value When the definition length is 17 bytes or more First 16 bytes of table data^# or the initial value
11	MVARCHAR	Character string with a length of 0	When the length of the character string or data is 16 bytes or less Table data^# or initial value When the length of the character string or data is 17 bytes or more First 16 bytes of table data^# or the initial value
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	When the length of the character string or data is 16 bytes or less Table data^# or initial value When the length of the character string or data is 17 bytes or more First 16 bytes of table data^# or the initial value

#: For a column of the CHAR or VARCHAR type for which a character set has been specified, data is stored in the specified character set.

16.3.2 Optimization information to be collected