16.4.1 Information to be specified in the optimization information parameter file

Table 16-6 lists the items to be specified in the optimization information parameter file, and Table 16-7 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

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 8 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 8 Installation and Design Guide.
NLEVELSpecifies the number of levels for the index.
See the HiRDB Version 8 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-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-name1) FROM table-name
Multicolumn index:
WITH W1(WC1,WC2,...) AS (SELECT DISTINCT index-column-name-11,index-column-name-21,... 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-name2) 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 column3 is 32,000 bytes or less

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

  • When the definition length of the column3 is more than 32,000 bytes

 WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,2554)) 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 column3 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 column3 specified for the column name is in the range 256-32,000 bytes

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

  • When the definition length of the column3 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 column3 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 column3 specified for the column name is in the range 256-32,000 bytes

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

  • When the definition length of the column3 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 ASC LIMIT 1

MAX_VALUE5Specifies the maximum column value.
The following SQL statement can be used to obtain the maximum column value:
SELECT MAX(column-name6) FROM table-name
MIN_VALUE5Specifies the minimum column value.
The following SQL statement can be used to obtain the minimum column value:
SELECT MIN(column-name6) FROM table-name
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,2554).
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,2554).
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.
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