Hitachi

Hitachi Advanced Database Application Development Guide


6.1.1 About access paths

An access path is the execution plan that is used when HADB executes an SQL statement. By checking the access paths, you can determine how the SQL statements to be executed will be processed by HADB.

Organization of this subsection

(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

[Figure]

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

[Figure]

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.

(2) SQL statements for which access paths are output

Access path information is output for the following SQL statements:

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