16.3.1 Optimization information to be collected

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 collectedClassificationDescriptionSQL statement that improves precision of optimizationProcessing that has adverse effects on integrity between registered optimization information and database statusItem name*
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 operation

NPAGES
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 usedNIPAGES
5Number of index levelsIMaximum number of index levels in each single server or back-end serverNLEVEL
6SequentialnessIDegree of sequentialness of the row data stored in order of index key valuesSEQ_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 informationMAX_VALUE, MIN_VALUE
11Number of unique column valuesCNumber of unique column valuesNUNIQUE
12Statistical information cache sizeTWork area used by the HiRDB system[Figure][Figure][Figure]
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.
*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 Information to be specified in the optimization information parameter file.

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 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-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 value in sectionMaximum column value in the corresponding section