6.1.5 Information displayed in the details view

(a) Information related to table retrieval methods

One of the following is displayed as the information related to table retrieval methods:

• TABLE SCAN

  A table scan is performed in the table retrieval processing.

• INDEX SCAN(schema-name.table-identifier(query-name-or-correlation-name))

  An index scan is performed in the table retrieval processing. If there is a query name or a correlation name, it is displayed.

• KEY SCAN

  A key scan is performed in the table retrieval processing.

For details about table scans, index scans, and key scans, see 5.1 How to retrieve tables.

Output example

<<Detail   >>
 
QUERY : 1
     3 INDEX SCAN(ADBUSER01.T1)
       INDEX NAME        : IDX_C1C2
        INDEX TYPE       : B-TREE
        INDEX COLUMN     : C1 ASC (IN)
        INDEX COLUMN     : C2 ASC (>)
       INDEX NAME        : RIDX_C2
        INDEX TYPE       : RANGE
        SKIP COND        : CHUNK (HASH)
        INDEX COLUMN     : C2

Explanation:

An index scan is performed in the table T1 retrieval processing.

(b) Information related to indexes

The following shows an output format for the information related to indexes.

Output format (B-tree index)

INDEX NAME        : B-tree-index-name (uniqueness-constraint-information)
 INDEX TYPE       : index-type
 INDEX COLUMN     : indexed-column-name key-value-sort-order (range-search-condition)

• uniqueness-constraint-information

  If this B-tree index is a unique index, uniqueness constraint information is displayed. One of the following is displayed:

  UNIQUE: This unique index does not violate the uniqueness constraint.

  UNIQUE INVALID: This unique index violates the uniqueness constraint.

• index-type

  For a B-tree index, B-TREE is displayed as the index type.

• key-value-sort-order

  Displays the sort order for the key values of the B-tree index that was specified when the B-tree index was defined. One of the following is displayed:

  ASC: The key values are sorted in ascending order.

  DESC: The key values are sorted in descending order.

• range-search-condition

  Displays one of the following as the range search condition:

  =, <, <=, >, >=, =ANY, BETWEEN({<,<|<,<=|<=,<|<=,<=}), IN, LIKE, IS NULL

Rules for search condition output

• If no range search condition is specified for an indexed column, none is displayed.

• If IN table-subquery or quantified-predicate=SOME is specified as the range search condition, =ANY is displayed.

• If the left side of the comparison predicate is not a single column specification, the HADB server performs equivalent exchange of the search condition. The comparison operator of the comparison predicate after equivalent exchange is output as the search condition.

  Example:

  Search condition specified in WHERE clause of SELECT statement

  WHERE 10 < C1

  Search condition after equivalent exchange by HADB server

  WHERE C1 > 10

  Information output as search condition in access path information

  INDEX COLUMN: C1 ASC (>)

Output format (text index)

INDEX NAME        : text-index-name
 INDEX TYPE       : index-type
 INDEX COLUMN     : indexed-column-name

• index-type

  For a text index, TEXT is displayed as the index type.

Output format (range index)

INDEX NAME        : range-index-name
 INDEX TYPE       : index-type
 SKIP COND        : range-index-condition-type
 INDEX COLUMN     : indexed-column-name

• index-type

  For a range index, RANGE is displayed as the index type.

• range-index-condition-type

  Displays one of the following as the range index condition to be used:

  • CHUNK: The chunk skip condition is used.

  • SEGMENT: The segment skip condition is used.

  • CHUNK AND SEGMENT: The chunk skip condition and the segment skip condition are both used.

  When a hash join is performed, (HASH) is output if a range index that is defined for a column that is to be matched with a hash table is used. When hash execution is performed as the processing method for a subquery, (HASH) is output if a range index that is defined for a column that is to be matched with a hash table is used.

  Output example

  SKIP COND        : CHUNK (HASH)

  For details about a hash join, see 5.5.2 About hash join.

  For details about hash execution as the processing method for a subquery, see either (4) Hash execution in 5.6.1 Methods for processing subqueries that do not contain an external reference column or (3) Hash execution in 5.6.3 Methods for processing subqueries that contain an external reference column.

Output example

<<Detail   >>
 
QUERY : 1
     3 INDEX SCAN(ADBUSER01.T1)
       INDEX NAME        : IDX_C1C2        ...1
        INDEX TYPE       : B-TREE          ...2
        INDEX COLUMN     : C1 ASC (IN)     ...3
        INDEX COLUMN     : C2 ASC (>)      ...3
       INDEX NAME        : RIDX_C2         ...4
        INDEX TYPE       : RANGE           ...5
        SKIP COND        : CHUNK (HASH)    ...6
        INDEX COLUMN     : C2              ...7

Explanation:

1. Name of the index to be used

2. Type of the index displayed by INDEX NAME in 1 In this example, IDX_C1C2 is a B-tree index because B-TREE is displayed.

   The information displayed in 1 and 2 indicates that an index scan is performed using B-tree index IDX_C1C2.

3. Information about B-tree index IDX_C1C2:

   C1, C2: Indexed column

   ASC: Key value sort order

   (IN), (>): Range search condition specification

4. Name of the index to be used

5. Type of the index displayed by INDEX NAME in 4 In this example, RIDX_C2 is a range index because RANGE is displayed.

6. Type of range index condition to be used

   CHUNK: Indicates that range index RIDX_C2 is used for a chunk skip condition.

   (HASH): When a hash join is performed, (HASH) indicates that the range index RIDX_C2 that is defined for a column that is to be matched with a hash table is used. When hash execution is performed as the processing method for a subquery, (HASH) indicates that the range index RIDX_C2 that is defined for a column that is to be matched with a hash table is used.

7. Indexed column of range index RIDX_C2

Note

• Information about the B-tree index is displayed when an index scan or a key scan is performed for table retrieval.

  If there are multiple indexed columns, information is displayed for each indexed column. In such a case, the information is displayed in the order the indexed columns were defined in the CREATE INDEX statement.

• Information about a text index is displayed when an index scan is implemented as the table retrieval method.

• Information about a range index is displayed whenever there is a range index condition.

<<Detail   >>
 
QUERY : 1
     3 HASH JOIN                             ← Table joining method
       JOIN TYPE         : INNER JOIN        ← Join type
       BUILD COLUMN      : ADBUSER01.T1.C3             ← Hash retrieval information
       PROBE COLUMN      : ADBUSER01.T2.C3             ← Hash retrieval information

(a) Table joining method

One of the following is displayed as the table joining method:

• NESTED LOOP JOIN

  A nested loop join is performed in the table join processing.

• HASH JOIN

  A hash join is performed in the table join processing.

  If there is an = condition that compares columns in two tables, a hash join might be performed. If there are rows that cannot be processed in the hash table area, work tables might be created.

For details about table joining methods, see 5.5 Table joining methods.

Output example

<<Detail   >>
 
QUERY : 1
     3 HASH JOIN
       JOIN TYPE         : INNER JOIN
       BUILD COLUMN      : ADBUSER01.T1.C3
       PROBE COLUMN      : ADBUSER01.T2.C3

Explanation:

A hash join is applied as the table joining method.

(b) Join type (JOIN TYPE)

One of the following is displayed as JOIN TYPE:

• CROSS JOIN

  A cross join is specified.

• INNER JOIN

  An inner join is specified.

• LEFT OUTER JOIN

  An outer join performed by a LEFT OUTER JOIN is specified.

• RIGHT OUTER JOIN

  An outer join performed by a RIGHT OUTER JOIN is specified.

• FULL OUTER JOIN(LEFT)

  An outer join performed by a FULL OUTER JOIN is specified.

• FULL OUTER JOIN(RIGHT)

  An outer join performed by a FULL OUTER JOIN is specified.

For details about each join method, see the topic Specification format and rules for joined tables in the manual HADB SQL Reference.

Output example

<<Detail   >>
 
QUERY : 1
     3 HASH JOIN
       JOIN TYPE         : INNER JOIN
       BUILD COLUMN      : ADBUSER01.T1.C3
       PROBE COLUMN      : ADBUSER01.T2.C3

Explanation:

An inner join is performed.

Important

INNER JOIN or CROSS JOIN might be converted to a comma join during execution of an SQL statement. In this case, the join type (JOIN TYPE) is not displayed. For details about a comma join, see Explanation of specification format in Specification format and rules for FROM clauses in the manual HADB SQL Reference.

The output rules are as follows:

• For an SQL statement that creates an internal derived table, the results of expanding the internal derived table are output as the table name and correlation name of the outer table columns and inner table columns for the hash join. For details about internal derived tables and the internal derived table expansion rules, see Internal derived tables in the manual HADB SQL Reference.

(c) Hash retrieval information (BUILD COLUMN and PROBE COLUMN)

When hash join is performed, the following information is displayed as hash retrieval information:

• BUILD COLUMN

  Information about the joined columns in the outer table

• PROBE COLUMN

  Information about the joined columns in the inner table

Output example

<<Detail   >>

QUERY : 1
     3 HASH JOIN
       BUILD COLUMN      : ADBUSER01.T1.C1(CREATE FILTER 1)    ...1
       PROBE COLUMN      : ADBUSER01.T2.C1(USE FILTER 1)       ...2

Explanation:

1. Column name ADBUSER01.T1.C1 of the joined column in the outer table during hash join is displayed.

2. Column name ADBUSER01.T2.C1 of the joined column in the inner table during hash join is displayed.

If a hash filter is applied during hash join, hash filter information is displayed in the underlined portion.

The output rules are as follows:

• For BUILD COLUMN or PROBE COLUMN, the column name is displayed in one of the following formats. Output of a correlation name has the highest priority among those column names. If a column name cannot be displayed, three asterisks (***) are displayed.

  • table-name.column-name

  • query-name.column-name

  • correlation-name.column-name

• If a hash filter is applied during hash join, (CREATE FILTER XXXXX) is displayed for BUILD COLUMN. XXXXX is the number of the hash filter created based on the column value for the displayed column name.

  (USE FILTER XXXXX) is displayed for PROBE COLUMN. XXXXX is the number of the hash filter to be used.

• For an SQL statement that creates an internal derived table, the results of expanding the internal derived table are output as the table name and correlation name of the outer table columns and inner table columns for the hash join. For details about internal derived tables and the internal derived table expansion rules, see Internal derived tables in the manual HADB SQL Reference.