6.1.1 About access paths

(1) Example of displayed access path information

The following figure shows an example of displayed access path information.

Figure 6‒1: Example of displayed access path information

Explanation:

Access path information is displayed in three views: tree view, details view, and identification information view.

• Tree view

  The information displayed in tree view includes table search methods, table joining methods, and information about work tables. Related items are displayed in tree format.

  Access path information is displayed for each query. Each query is assigned a query tree number.

  <<Tree View>> is displayed as the header for the tree view section.

• Details view

  The details view displays detailed information about the items in tree view. The tree row numbers in the details view correspond to the tree row numbers in tree view. In this example, details view displays detailed information about the item assigned tree row number 7 in tree view.

  <<Detail >> is displayed as the header for the details view section.

• Identification information view

  The identification information view displays SQL statement identification information. SQL statement identification information is information used to identify the SQL statement for which HADB acquired access path statistical information. Based on the information in the identification information view, you can identify the correlation between the output access path information and the output access path statistical information.

  <<SQL Info >> is displayed as the header for the identification information view section.

  For details about access path statistical information, see Examples of output of and output items for access path statistical information in the HADB Setup and Operation Guide.

■ Query trees, query tree numbers, and tree row numbers

Explanation:

• Query tree

  Access path information is output in a form of a query tree for each query (query specification and table value constructor).

• Query tree number

  Each query tree is assigned a number (called a query tree number) for identification.

  A query tree corresponding to a subquery is assigned a query tree number of 2 or larger. Note, however, that a query tree number of 1 or larger is assigned to a query tree that corresponds to a subquery if the following conditions are met:

  • VALUES (in which a subquery is specified) is specified in the INSERT statement.

  • A subquery is specified in the PURGE CHUNK statement.

  Note

  • Whether query tree numbers are output in exact ascending order (1, 2, ...) depends on the SQL statement to be run.

  • A query tree number of 0 is output for query trees that are not contained in the queries in the SQL statement.

• Tree row number

  A number assigned to each row in the tree.

To Page Top

(2) SQL statements for which access paths are output

Access path information is output for the following SQL statements:

• SELECT

• UPDATE

• INSERT

  Access path information is output in the following circumstances:

  • A query expression body is specified in the INSERT statement.

  • VALUES is specified in the INSERT statement, and a subquery is specified.

• DELETE

• PURGE CHUNK

  Access path information is output when a subquery is specified in the PURGE CHUNK statement.

Access path information is output when you run these SQL statements.

To Page Top