Scalable Database Server, HiRDB Version 8 Command Reference

[Contents][Index][Back][Next]

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

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:
  • 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
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.

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
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:
  • 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_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:
  • 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_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:
  • 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_VALUE5 Specifies 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_VALUE5 Specifies 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

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.