16.3.2 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 collectedClassificationDescriptionSQL statement that improves precision of optimizationProcessing that has adverse effects on integrity between registered optimization information and database statusItem name#1
1Number of rowsTTotal number of rows in the target tableSQL statement containing joinSQL statement that involves an updating operation and that changes the number of rows in the target tableNROWS
2Number of data pagesTTotal number of row data storage pages in the target tableSQL statement that contains columns comprising the index in the search conditionSQL statement that involves an updating operationNPAGES
3Number of unique index key valuesINull value (when the optimization information parameter file is used, the specified value is registered)

NENTRY
4Number of index pagesITotal number of index storage pages used

NIPAGES
5Number of index levelsIMaximum number of index levels in each single server or back-end server

NLEVEL
6SequentialnessIDegree of sequentialness of the row data stored in order of index key values

SEQ_RATIO
7Number of null valuesCNumber of null values in the columnsSQL statement that contains a column comprising a single-column index or the first column comprising a multicolumn indexNNULLS
8Maximum number of duplicate column valuesCMaximum number of duplicate values for a columnN_MAX_DUP_KEY
9Minimum number of duplicate column valuesCMinimum number of duplicate values for a columnN_MIN_DUP_KEY
10Column value distribution informationCColumn value distribution information#2MAX_VALUE, MIN_VALUE, SECTION_COUNT, SEC_TOTAL_COUNT, SEC_UNIQUE, SEC_MAX_VALUE
11Number of unique column valuesCNumber of unique column valuesNUNIQUE
12Statistical information cache sizeTWork 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 collectedDescription
1Number of null valuesAll rows in the target table
2Number of table rowsTotal number of pages storing the row data of the target table
3Column data lengthData length of the column
4Column data typeData type of the column
5Number 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.
6Maximum column valueMaximum value of the column#
7Minimum column valueMinimum value of the column#
8Degree of cumulation between sectionsTotal number of elements from section 1 through the corresponding section
9Number of unique values in sectionNumber of unique column values in the corresponding section
10Maximum column value in sectionMaximum 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 typeInitial valueData to be stored
    1INT[EGER]0Table data# or initial value
    2SMALLINT0
    3[LARGE] DEC[IMAL]+0
    4FLOAT or DOUBLE PRECISION0
    5SMALLFLT or REAL0
    6CHAR[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
    7VARCHARCharacter 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
    8NCHAR
    • When the definition length is 8 bytes or less
      As many 0s as the definition length [Figure] 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
    9NVARCHARCharacter 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
    10MCHAR
    • 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
    11MVARCHARCharacter 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
    12DATE0000-00-00Table data# or initial value
    13TIME00:00:00
    14INTERVAL YEAR TO DAY+00000000​.
    15INTERVAL HOUR TO SECOND+000000.
    16TIMESTAMP0000-00-00 00:00:00.0....0
    17BINARYData 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.