38.5.16 #SET OPT REPORT (displays index, work table, and access path information) : Hitachi Advanced Database Command Reference

(1) Function

This subcommand specifies whether index, work table, and access path information for any of the following SQL statements is to be displayed when the SQL statement is to execute:

DELETE statement
INSERT statement

In the following cases, index information, work table information, and access path information are displayed:
- A query expression body is specified in the INSERT statement.
- VALUES is specified in the INSERT statement, and a subquery is also specified
PURGE CHUNK statement

If a subquery is specified in the PURGE CHUNK statement, index information, work table information, and access path information are displayed.
SELECT statement
UPDATE statement

If the executed SQL statement does not create a work table, no work table information is displayed.

If an index specification is specified for a table reference, whether that index specification was applied is displayed as the index information. For details about the index specification, see Specification format and rules for index specifications in the manual HADB SQL Reference.

For details about the access path information, see How to use access paths (how to use SQL statement execution plans) in Tuning Application Programs in the HADB Application Development Guide.

The initial value is OFF (index, work table, and access path information is not displayed).

To Page Top

(2) Specification format

#SET OPT REPORT {ON
                   [TYPE = {INDEX|PATH|ALL}]
                   [EXEC = {NORMAL|PREPARE}]
                |OFF};

To Page Top

(3) Explanation of options

ON:

Displays index, work table, and access path information.

[TYPE = {INDEX|PATH|ALL}]:

Specifies the type of information that is to be displayed:

INDEX: Display index and work table information.

PATH: Display only access path information.

ALL: Display index, work table, and access path information.

[EXEC = {NORMAL|PREPARE}]:

Specifies whether the SQL statement is to be executed after the information has been displayed:

NORMAL: Execute the SQL statement after the information has been displayed.

PREPARE: For the following SQL statements, display the information, but do not execute the SQL statement:

DELETE statement
INSERT statement
PURGE CHUNK statement
SELECT statement
UPDATE statement

Any other SQL statement will be executed, even though EXEC = PREPARE is specified.

OFF:

Does not display index, work table, or access path information.

To Page Top

(4) Examples

Executing an SQL statement after displaying index and work table information

COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
#SET OPT REPORT ON;                                        ...1
KFAA96499-I OPT REPORT changed  ( OFF -> ON )
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
SELECT * FROM "T1" WHERE "C1"=100 ORDER BY "C2";           ...2
KFAA96405-I The B-tree index "INDX01" will be used.
 (table name="ADBUSER01"."T1" as "***")                    ...2
KFAA96406-I Executing this SQL will create 1 work tables.  ...3
 retrieval-results                                         ...4
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+

Explanation:

Executes #SET OPT REPORT specifying that index and work table information is to be displayed.
Executes the SQL statement.
Displays the index and work table information as messages. The displayed messages indicate the following:

• B-tree index INDX01 is used when this SQL statement executes.

• One work table is created when this SQL statement executes.
Displays the execution results of the SQL statement.

Executing an SQL statement after displaying all information

COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
#SET OPT REPORT ON TYPE = ALL EXEC = NORMAL;               ...1
KFAA96499-I OPT REPORT changed  ( OFF -> ON )
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
SELECT * FROM "T1" WHERE "C1"=100 ORDER BY "C2";           ...2
KFAA96405-I The B-tree index "INDX01" will be used.
 (table name="ADBUSER01"."T1" as "***")                    ...2
KFAA96406-I Executing this SQL will create 1 work tables.  ...3
 access-path-information                                   ...4
KFAA96412-I The access path was output.                    ...5
 retrieval-results                                         ...6
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+

Explanation:

Executes #SET OPT REPORT specifying that index, work table, and access path information is to be displayed.
Executes the SQL statement.
Displays the index and work table information as messages. The displayed messages indicate the following:

• B-tree index INDX01 is used when this SQL statement executes.

• One work table is created when this SQL statement executes.
Displays the access path information. For details about how to interpret the displayed access path information, see Examples of access paths in How to use access paths (how to use SQL statement execution plans) in Tuning Application Programs in the HADB Application Development Guide.
Displays a message indicating that the access path information has been displayed.
Displays the execution results of the SQL statement.

Displaying only access path information without executing an SQL statement

COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
#SET OPT REPORT ON TYPE = PATH EXEC = PREPARE;             ...1
KFAA96499-I OPT REPORT changed  ( OFF -> ON )
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+
SELECT * FROM "T1" WHERE "C1"=100 ORDER BY "C2";           ...2
 access-path-information                                   ...3
KFAA96412-I The access path was output.                    ...4
COMMAND ?     +----2----+----3----+----4----+----5----+----6----+----7----+

Explanation:

Executes #SET OPT REPORT specifying that access path information is to be displayed.
Executes the SQL statement.
Displays the access path information. For details about how to interpret the displayed access path information, see Examples of access paths in How to use access paths (how to use SQL statement execution plans) in Tuning Application Programs in the HADB Application Development Guide.
Displays a message indicating that the access path information has been displayed.

To Page Top

(5) Messages displayed as index and work table information

This subsection explains the messages that are displayed as index and work table information.

Messages that are output as index information

The following table lists and describes the messages that are output as index information. If no index is used, no messages are output.

Table 38‒6: List of messages that are output as index information
No.	Message that is output	Contents of index information
1	`KFAA96405-I`	If the index specification is not specified B-tree indexes are used for retrieval. If the index specification (`WITH` `INDEX`) is specified Because the index specification is invalid,^# B-tree indexes that are not specified are used for retrieval.
2	`KFAA96407-I`	No indexes (B-tree indexes, text indexes, or range indexes) are used for retrieval.
3	`KFAA96408-I`	Range indexes are used for retrieval.
4	`KFAA96409-I`	The index specification (`WITH` `INDEX`) is applied and the specified B-tree indexes are used for retrieval.
5	`KFAA96410-I`	The index specification (`WITHOUT` `INDEX`) is applied, and use of the specified B-tree or text indexes is suppressed.
6	`KFAA96411-I`	The index specification is invalid.
7	`KFAA96413-I`	If the index specification is not specified: Text indexes are used for retrieval. If the index specification (`WITH` `INDEX`) is specified: Because the index specification is invalid,^# text indexes that are not specified are used for retrieval.
8	`KFAA96414-I`	The index specification (`WITH` `INDEX`) is applied, and the specified text indexes are used for retrieval.

#

The index specification is ignored in the following cases:

A nonexistent index name was specified.
A B-tree index with the null-value exclusion specification is specified, but the specified condition contains a null value within the index retrieval range.
HADB cannot effectively use text indexes, such as when text indexes are specified but no LIKE predicate that can be evaluated in the specified text indexes is specified.

Message that is output as work table information

If a work table is created, the KFAA96406-I message is output as work table information. If no work table is created, this message is not output.

To Page Top

(6) Notes

The same index information might be displayed multiple times for a table that was specified in FULL OUTER JOIN for a joined table. For details about the index information, check the access path information.

To Page Top