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
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 |
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 8 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 8 Installation and Design Guide. |
NLEVEL | Specifies 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_RATIO | Specifies 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. |
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,2554)) 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,2554)) 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,2554)) FROM table-name
WITH W1(WSUBCLM) AS (SELECT SUBSTR(column-name,1,2554)) FROM table-name) |
MAX_VALUE5 | Specifies the maximum column value.
|
MIN_VALUE5 | Specifies the minimum column value.
|
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