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
Classification | Item | Description |
---|---|---|
Table optimization information | NROWS | Number of rows in table |
NPAGES | Number of data pages in table | |
Index optimization information | INDEX | Index name |
NIPAGES | Number of index pages | |
NLEVEL | Number of index levels | |
SEQ_RATIO | Degree of index sequentiality | |
NENTRY | Number of index key values | |
Column optimization information | COLUMN | Column name |
NUNIQUE | Number of unique column values | |
NNULLS | Number of null values in column | |
N_MAX_DUP_KEY | Maximum number of duplicate column values | |
N_MIN_DUP_KEY | Minimum number of duplicate column values | |
MAX_VALUE | Maximum column value | |
MIN_VALUE | Minimum column value | |
Section distribution information | SECTION_COUNT | Number of sections for section distribution information |
SECTION_NO | Section number | |
SEC_TOTAL_COUNT | Cumulative frequency of the number of elements for the section | |
SEC_UNIQUE | Number of unique values in the section | |
SEC_MAX_VALUE | Maximum value in the section |
Table 16-7 Descriptions of items in the optimization information parameter file
Item | Description |
---|---|
NROWS | Specifies 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 |
NPAGES | Specifies 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. |
INDEX | Specifies the name of the index. The specified index must be defined in the table specified with the -t option. |
NIPAGES | Specifies 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. |
NLEVEL | Specifies 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_RATIO | Specifies 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. |
NENTRY | Specifies the number of key values in the index:
|
COLUMN | Specifies 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.). |
NUNIQUE | Specifies the number of unique values in the column.
|
NNULLS | Specifies 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:
SELECT COUNT(*) FROM table-name WHERE column-name IS NULL
WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,255#4)) FROM table-name) |
N_MAX_DUP_KEY | Specifies 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:
WITH W1(WCOUNT) AS (SELECT COUNT(column-name) FROM table-name
SELECT COUNT(SUBSTR(column-name,1,255#4)) FROM table-name
WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,2554)) FROM table-name) |
N_MIN_DUP_KEY | Specifies 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:
WITH W1(WCOUNT) AS (SELECT COUNT(column-name) FROM table-name
SELECT COUNT(SUBSTR(column-name,1,255#4)) FROM table-name
WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,255#4)) FROM table-name) |
MAX_VALUE#5 | Specifies the maximum column value.
|
MIN_VALUE#5 | Specifies the minimum column value.
|
SECTION_COUNT | Specifies 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_NO | Specifies the number of a section in the range from 1 to the total number of sections. |
SEC_TOTAL_COUNT | Specifies 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 ![]() ![]() 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_UNIQUE | Specifies the number of unique column values in the section. |
SEC_MAX_VALUE | Specifies the maximum column value in the section. |
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