16.4.1 Items in the optimization information parameter file

The table below lists the items to be specified in the optimization information parameter file, and Table 16-7 Descriptions of items in the optimization information parameter file provides a description of each item.

Table 16-6 Items specified in the optimization information parameter file

ClassificationItemDescription
Table optimization informationNROWSNumber of rows in table
NPAGESNumber of data pages in table
Index optimization informationINDEXIndex name
NIPAGESNumber of index pages
NLEVELNumber of index levels
SEQ_RATIODegree of index sequentiality
NENTRYNumber of index key values
Column optimization informationCOLUMNColumn name
NUNIQUENumber of unique column values
NNULLSNumber of null values in column
N_MAX_DUP_KEYMaximum number of duplicate column values
N_MIN_DUP_KEYMinimum number of duplicate column values
MAX_VALUEMaximum column value
MIN_VALUEMinimum column value
Section distribution informationSECTION_COUNTNumber of sections for section distribution information
SECTION_NOSection number
SEC_TOTAL_COUNTCumulative frequency of the number of elements for the section
SEC_UNIQUENumber of unique values in the section
SEC_MAX_VALUEMaximum value in the section

Table 16-7 Descriptions of items in the optimization information parameter file

ItemDescription
NROWSSpecifies the number of rows in the table.
The following SQL statement can be used to obtain the number of rows in a table:
SELECT COUNT(*) FROM table-name
NPAGESSpecifies the number of data pages used in the table.
Execute the database condition analysis utility or see the HiRDB Version 9 Installation and Design Guide.
INDEXSpecifies the name of the index.
The specified index must be defined in the table specified with the -t option.
NIPAGESSpecifies the number of index pages used for the index.
Execute the database condition analysis utility or see the HiRDB Version 9 Installation and Design Guide.
NLEVELSpecifies the number of levels for the index.
See the HiRDB Version 9 Installation and Design Guide. In the case of a cluster key, this value can be obtained by the database condition analysis utility.
SEQ_RATIOSpecifies the degree of sequentiality of the index.
Permitted value range is 0 to 100. If data is stored in the order of index key values, specify a value of 100; if data is stored in random order, specify a value of 0. As this value increases, the index is used more frequently.
NENTRYSpecifies the number of key values in the index:
  • For a unique index, specifies the number of rows in the table
  • For a non-unique index, specifies the number of key values in the index, excluding duplicate values
The following SQL statements can be used to obtain the number of key values in the table:
Single-column index:
SELECT COUNT(DISTINCT index-column-name#1) FROM table-name
Multicolumn index:
WITH W1(WC1,WC2,...) AS (SELECT DISTINCT index-column-name-1#1,index-column-name-2#1,... FROM table-name) SELECT COUNT(*) FROM W1
COLUMNSpecifies the name of a column.
The specified column must be contained in the table specified with the -t option (However, LOB columns and columns of abstract data type cannot be specified.).
NUNIQUESpecifies the number of unique values in the column.
The following SQL statement can be used to obtain the number of unique values in a column:
SELECT COUNT(DISTINCT column-name#2) FROM table-name
NNULLSSpecifies the number of null values in the column.
The following SQL statement can be used to obtain the number of null values in a column:
  • When the definition length of the column#3 is 32,000 bytes or less

 SELECT COUNT(*) FROM table-name WHERE column-name IS NULL

  • When the definition length of the column#3 is more than 32,000 bytes

 WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,255#4)) FROM table-name)
 SELECT COUNT(*) FROM W1 WHERE WSUBCLM IS NULL

N_MAX_DUP_KEYSpecifies the maximum number of duplicate column values in the table.
The following SQL statement can be used to obtain the maximum number of duplicate column values:
  • When the definition length of the column#3 specified for the column name is 255 bytes or less

 WITH W1(WCOUNT) AS (SELECT COUNT(column-name) FROM table-name
   WHERE column-name IS NOT NULL GROUP BY column-name)
   SELECT MAX(WCOUNT) FROM W1

  • When the definition length of the column#3 specified for the column name is in the range 256 to 32,000 bytes

 SELECT COUNT(SUBSTR(column-name,1,255#4)) FROM table-name
   WHERE column-name IS NOT NULL GROUP BY SUBSTR(column-name,1,255#4)
   ORDER BY 1 DESC LIMIT 1

  • When the definition length of the column#3 specified for the column name is more than 32,000 bytes

 WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,2554)) FROM table-name)
   SELECT COUNT(WSUBCLM) FROM W1 WHERE WSUBCLM IS NOT NULL
   GROUP BY WSUBCLM ORDER BY 1 DESC LIMIT 1

N_MIN_DUP_KEYSpecifies the minimum number of duplicate column values in the table.
The following SQL statement can be used to obtain the minimum number of duplicate column values:
  • When the definition length of the column#3 specified for the column name is 255 bytes or less

 WITH W1(WCOUNT) AS (SELECT COUNT(column-name) FROM table-name
   WHERE column-name IS NOT NULL GROUP BY column-name)
   SELECT MIN(WCOUNT) FROM W1

  • When the definition length of the column#3 specified for the column name is in the range 256 to 32,000 bytes

 SELECT COUNT(SUBSTR(column-name,1,255#4)) FROM table-name
   WHERE column-name IS NOT NULL GROUP BY SUBSTR(column-name,1,255#4)
   ORDER BY 1 ASC LIMIT 1

  • When the definition length of the column#3 specified for the column name is more than 32,000 bytes

 WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,255#4)) FROM table-name)
   SELECT COUNT(WSUBCLM) FROM W1 WHERE WSUBCLM IS NOT NULL
   GROUP BY WSUBCLM ORDER BY 1 ASC LIMIT 1

MAX_VALUE#5Specifies the maximum column value.
The following SQL statement can be used to obtain the maximum column value:
SELECT MAX(column-name#6) FROM table-name
MIN_VALUE#5Specifies the minimum column value.
The following SQL statement can be used to obtain the minimum column value:
SELECT MIN(column-name#6) FROM table-name
SECTION_COUNTSpecifies the number of sections for section distribution information.
If the number of column value types does not exceed 100, specify the actual number of column value types. If the number of column value types is more than 100, specify 100.
This item must be specified before the section distribution information. The specified value must be the same as the number of specified section distribution information items.
SECTION_NOSpecifies the number of a section in the range from 1 to the total number of sections.
SEC_TOTAL_COUNTSpecifies the cumulative frequency of the number of elements for the section.
This value can be obtained from the following formula:
(Number of column value types[Figure] 100) [Figure] the section number of the applicable section
If the number of column value types is not evenly divisible by 100, the number of column value types is specified for the cumulative frequency histogram when section distribution information for the last section is defined.
SEC_UNIQUESpecifies the number of unique column values in the section.
SEC_MAX_VALUESpecifies the maximum column value in the section.
#1: If the definition length of the column specified for the index column name is 256 bytes or more, replace index-column-name with SUBSTR (index-column-name,1,255#4).
#2: If the definition length of the column specified for the column name is 256 bytes or more, replace column-name with SUBSTR (column-name,1,255#4).
#3: Definition lengths are in bytes. For the national character string data type, the value is definition length [Figure]2.
#4: If the data type of the column is character string data or BINARY, the value is 255. If it is national character string or mixed character string, the value is 127.
#5: MAX_VALUE is stored in byte 32 of the RANGE_VALUES column in the SQL_COLUMN_STATISTICS data dictionary table. MIN_VALUE is stored in byte 48 of the RANGE_VALUES column in the SQL_COLUMN_STATISTICS data dictionary table. Note that if the data type of the column specified by its column name is DECIMAL or NUMERIC and the precision is 32 digits or more, the data is stored at the location obtained by adding 32 bytes to the column's definition length.#3
#6: If the definition length of the column is 256 bytes or more, make the following replacement:
  • If the data type of the column is CHAR or BINARY, replace column-name with SUBSTR(column-name,1,255).
  • If the data type of the column is NCHAR or MCHAR, replace column-name with SUBSTR(column-name,1,127).
  • If the data type of the column is VARCHAR, NVARCHAR, or MVARCHAR, replace column-name with the following specification:

 CASE LENGTH(column-name) WHEN  0 THEN ''
    WHEN  1 THEN SUBSTR(column-name,1,1)  WHEN  2 THEN SUBSTR(column-name,1,2)
    WHEN  3 THEN SUBSTR(column-name,1,3)  WHEN  4 THEN SUBSTR(column-name,1,4)
    WHEN  5 THEN SUBSTR(column-name,1,5)  WHEN  6 THEN SUBSTR(column-name,1,6)
    WHEN  7 THEN SUBSTR(column-name,1,7)  WHEN  8 THEN SUBSTR(column-name,1,8)
    WHEN  9 THEN SUBSTR(column-name,1,9)  WHEN 10 THEN SUBSTR(column-name,1,10)
    WHEN 11 THEN SUBSTR(column-name,1,11)  WHEN 12 THEN SUBSTR(column-name,1,12)
    WHEN 13 THEN SUBSTR(column-name,1,13) ELSE SUBSTR(column-name,1,14) END