6.1.4 Information displayed in the tree view : Hitachi Advanced Database Application Development Guide

(1) SQL statements executed

One of the following is displayed:

SELECT STATEMENT

A SELECT statement is to be executed.
UPDATE STATEMENT

An UPDATE statement is to be executed.
INSERT STATEMENT

An INSERT statement is to be executed.
DELETE STATEMENT

A DELETE statement is to be executed.
PURGE CHUNK STATEMENT

PURGE CHUNK statement is to be executed.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: A SELECT statement is executed.

To Page Top

(2) Subquery processing methods

One of the following is displayed:

SUBQUERY

A subquery processing method other than nested loop execution or hash execution is applied.
SUBQUERY LOOP

Nested loops work table execution or nested loops row value execution is applied as the subquery processing.
SUBQUERY HASH

Hash execution is applied as the subquery processing method.

If SUBQUERY HASH is followed by FILTER, a hash filter is applied during hash execution.

For details about how to process subqueries, see 5.6 How to process subqueries.

Output example

<<Tree View>>

     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-SUBQUERY HASH -FILTER
     4     |-QUERY SCAN(QUERY 2)
     5     +-TABLE SCAN(ADBUSER01.T1)
     6 
     7 QUERY : 2
     8   SUBQUERY HASH
     9   +-TABLE SCAN(ADBUSER01.T2)

Explanation:

Hash execution is applied as the subquery processing method.

Because SUBQUERY HASH is followed by FILTER in tree row number 3, a hash filter is applied during hash execution.

To Page Top

(3) Specification of derived tables

The following information is displayed:

DERIVED TABLE(correlation-name)

One of the following is specified:
- A derived table
- A viewed table (if a correlation name is specified)
- A query name (if a correlation name is specified)
DERIVED TABLE(query-name)

A query name is specified (without a correlation name).
DERIVED TABLE(table-identifier)

A viewed table is specified (without a correlation name)

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-DERIVED TABLE(D1)
     4     +-QUERY SCAN(QUERY 2)
     5 
     6 QUERY : 2
     7   DERIVED TABLE(D1)
     8   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     9   | |-TABLE SCAN(ADBUSER01.T2)
    10   | |-SORTING BYTE
    11   | +-LIMIT 10
    12   |-WORK TABLE SCAN(WORK TABLE 1)
    13   +-LIMIT 10

Explanation:: A derived table is specified in the SELECT statement. The correlation name is displayed in parentheses.

To Page Top

(4) Specification of set operations

The following information is displayed:

SET OPERATION

A set operation is specified.
Note
The preceding information is also displayed in the following circumstances:
- FULL OUTER JOIN is specified for joining tables
- An archivable multi-chunk table is specified
  
  When retrieving an archivable multi-chunk table, equivalent exchange of the SQL statement might result in it being automatically rewritten as an SQL statement that specifies a set operation. For details, see 5.12.4 Equivalent exchange of SQL statements that search archivable multi-chunk tables.
- Equivalent exchange related to OR conditions (equivalent exchange to a derived table for which the UNION ALL set operation is specified) is applied
  
  If a comma join or joined table is specified in the FROM clause and an OR condition is specified in the WHERE clause, the SQL statement might be automatically rewritten as a result of equivalent exchange to derived tables for which the UNION ALL set operation is specified. For details, see 5.11.3 Equivalent exchange for OR conditions (equivalent exchange to derived tables for which the UNION ALL set operation is specified).

Output example

<<Tree View>>
 
     1 QUERY : 0
     2   SELECT STATEMENT
     3   +-SET OPERATION
     4     |-QUERY SCAN(QUERY 1)
     5     +-QUERY SCAN(QUERY 2)
     6 
     7 QUERY : 1
     8   QUERY
     9   +-TABLE SCAN(ADBUSER01.T1)
    10 
    11 QUERY : 2
    12   QUERY
    13   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: A set operation is specified in the SELECT statement.

Note that SET OPERATION might be followed by the following item:

RECURSIVE

This item indicates that a recursive query will be run.

Output example

<<Tree View>>
 
     1 QUERY : 3
     2   SELECT STATEMENT
     3   +-DERIVED TABLE(REC)
     4     +-SET OPERATION -RECURSIVE
     5       |-QUERY SCAN(QUERY 1)
     6       +-QUERY SCAN(QUERY 2)
     7
     8 QUERY : 1
     9   QUERY
    10   |-CREATE GLOBAL WORK TABLE(WORK TABLE 1)
    11   | +-TABLE SCAN(ADBUSER01.T1)
    12   +-WORK TABLE SCAN(WORK TABLE 1)
    13
    14 QUERY : 2
    15   QUERY
    16   |-CREATE GLOBAL WORK TABLE(WORK TABLE 2)
    17   | +-WORK TABLE SCAN(WORK TABLE 2)
    18   +-WORK TABLE SCAN(WORK TABLE 2)

Explanation:

An item indicating that a recursive query will be run is included.

After SET OPERATION, the query information displayed first pertains to anchor members. The query information displayed next pertains to recursive members. In the preceding output example, QUERY 1 is the query information about anchor members, and QUERY 2 is the query information about recursive members.

To Page Top

(5) Set operation method specification

The following information is displayed:

SPECIFIC

Set operation method specification is enabled.

For details about set operation method specifications, see Specification format and rules for query expressions in the manual HADB SQL Reference.

Output example

<<Tree View>>
 
     1 QUERY : 0
     2   SELECT STATEMENT
     3   +-SET OPERATION -SPECIFIC
     4     |-QUERY SCAN(QUERY 1)
     5     +-QUERY SCAN(QUERY 2)
     6
     7 QUERY : 1
     8   QUERY
     9   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
    10   | |-TABLE SCAN(ADBUSER01.T1)
    11   | +-SORTING BYTE -DISTINCT
    12   +-WORK TABLE SCAN(WORK TABLE 1)
    13
    14 QUERY : 2
    15   QUERY
    16   |-CREATE LOCAL WORK TABLE(WORK TABLE 2)
    17   | |-TABLE SCAN(ADBUSER01.T2)
    18   | +-SORTING BYTE -DISTINCT
    19   +-WORK TABLE SCAN(WORK TABLE 2)

Explanation:: The set operation method specification specified in the SELECT statement is enabled.

To Page Top

(6) Query types

The following information is displayed:

QUERY

A query expression body other than a subquery or a derived table is specified.

Note

If an archivable multi-chunk table is specified as the table to be updated by an UPDATE statement or the table to be deleted by a DELETE statement, a query that retrieves the location table or system table (STATUS_CHUNKS) might be displayed.

Output example

<<Tree View>>
 
     1 QUERY : 0
     2   INSERT STATEMENT
     3   +-QUERY SCAN(QUERY 1)
     4 
     5 QUERY : 1
     6   QUERY
     7   +-TABLE SCAN(ADBUSER01.T2) -ORDER

Explanation:: A query expression body is specified in the INSERT statement.

To Page Top

(7) Work table creation information

One of the following is displayed:

CREATE GLOBAL WORK TABLE (WORK TABLE work-table-number)

A global work table is created.
CREATE LOCAL WORK TABLE (WORK TABLE work-table-number)

A local work table is created.

A unique work table number is assigned to each work table.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-SUBQUERY
     4   | +-CREATE GLOBAL WORK TABLE(WORK TABLE 1)
     5   |   |-QUERY SCAN(QUERY 2)
     6   |   +-SORTING BYTE
     7   +-INDEX SCAN(ADBUSER01.T1)
     8     +-SUBQUERY
     9       +-WORK TABLE SCAN(WORK TABLE 1)
    10 
    11 QUERY : 2
    12   SUBQUERY
    13   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: A global work table is created in the subquery processing.

To Page Top

(8) Subquery processing method specification

The following information is displayed:

SPECIFIC

Subquery processing method specification is enabled.

For details about subquery processing method specifications, see Specification format and rules for subqueries in the manual HADB SQL Reference.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-SUBQUERY -SPECIFIC
     4   | +-CREATE GLOBAL WORK TABLE(WORK TABLE 1)
     5   |   +-QUERY SCAN(QUERY 2)
     6   +-TABLE SCAN(ADBUSER01.T1(A))
     7     +-SUBQUERY
     8       +-WORK TABLE SCAN(WORK TABLE 1)
     9
    10 QUERY : 2
    11   SUBQUERY
    12   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: The subquery processing method specification specified in the SELECT statement is enabled.

To Page Top

(9) Subquery processing delegation specification

The following information is displayed:

DELEGATION

Subquery processing delegation specification is enabled.

For details about subquery processing delegation specifications, see Specification format and rules for subqueries in the manual HADB SQL Reference.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(ADBUSER01.T1(A))
     4     +-SUBQUERY LOOP -DELEGATION -USING CACHE
     5       +-QUERY SCAN(QUERY 2)
     6
     7 QUERY : 2
     8   SUBQUERY LOOP
     9   |-SUBQUERY LOOP -SPECIFIC -DELEGATION
    10   | +-QUERY SCAN(QUERY 3)
    11   +-TABLE SCAN(ADBUSER01.T1(B))
    12
    13 QUERY : 3
    14   SUBQUERY LOOP
    15   +-TABLE SCAN(ADBUSER01.T1(C))

Explanation:: The subquery processing delegation method specification specified in the SELECT statement is enabled.

To Page Top

(10) Subquery cache usage information

The following information is displayed:

USING CACHE

Cache is used to store the results of a subquery. Cache might be used when nested loops row value execution is used to process the subquery.

For details about nested loops row value execution, see (2) Nested loops row value execution in 5.6.3 Methods for processing subqueries that contain an external reference column.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(ADBUSER01.T1)
     4     +-SUBQUERY LOOP -USING CACHE
     5       +-QUERY SCAN(QUERY 2)
     6 
     7 QUERY : 2
     8   SUBQUERY LOOP
     9   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: Nested loops row value execution is used to process the subquery, and the results of the subquery are stored using cache.

To Page Top

(11) Specification of table function derived tables

The following information is displayed:

TABLE FUNCTION DERIVED TABLE(correlation-name)

A table function derived table is specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE FUNCTION DERIVED TABLE(T4)
     4     +-TABLE SCAN(ADBUSER01.T4)

Explanation:: A table function derived table is specified in the SELECT statement. The information in parentheses is the correlation name.

To Page Top

(12) Processing method for duplicate removal

The following information is displayed:

GLOBAL HASH UNIQUE

Duplication in the retrieval results will be eliminated by using one of the following methods:
- Hash execution for the method for processing the set operation
  
  For details about hash execution for the method for processing the set operation, see 5.8.1 Hash execution.
- Hash execution for the method for processing SELECT DISTINCT
  
  For details about hash execution for the method for processing SELECT DISTINCT, see 5.9.1 Hash execution.
- Global hash grouping for the grouping method
  
  For details about global hash grouping for the grouping method, see (2) Global hash grouping in 5.7.1 Hash grouping.
GLOBAL HASH UNIQUE is also displayed if work tables might be created because some rows cannot be processed in the hash table area.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-KEY SCAN(ADBUSER01.T1)
     4   |-GLOBAL HASH UNIQUE
     5   +-GROUPING

Explanation:: Global hash grouping is used for the grouping method to eliminate duplicate retrieval results.

To Page Top

(13) Grouping methods

One of the following is displayed:

GROUPING

Grouping that does not use work tables is performed.
SORT GROUPING

Sort grouping is performed.
GLOBAL HASH GROUPING

Global hash grouping is performed. If there are rows that cannot be processed in the hash table area, work tables might be created.
LOCAL HASH GROUPING

Local hash grouping is performed. If there are rows that cannot be processed in the hash grouping area, work tables might be created.

For details about grouping methods, see 5.7 Grouping methods.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-TABLE SCAN(ADBUSER01.T1)
     4   +-GLOBAL HASH GROUPING

Explanation:: Global hash grouping is performed during the grouping method.

Following the grouping method information, the following item might be displayed:

SPECIFIC

The grouping method specification specified in the GROUP BY clause is enabled.

Output example
```
<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4   | |-TABLE SCAN(ADBUSER01.T1)
     5   | +-SORTING BYTE
     6   |-WORK TABLE SCAN(WORK TABLE 1)
     7   +-LOCAL HASH GROUPING -SPECIFIC
```
Explanation:

The grouping method specification specified in the GROUP BY clause is enabled and LOCAL HASH GROUPING is applied as the grouping method. For details about the grouping method specification, see the topic Specification format and rules for GROUP BY clauses in the manual HADB SQL Reference.

INDEX

The grouping method that uses the characteristics of B-tree indexes is performed.

Output example
```
<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-KEY SCAN(ADBUSER01.T1)
     4   +-GROUPING -INDEX
```
Explanation:

The grouping method that uses the characteristics of B-tree indexes is performed for table T1.
COLUMN

The grouping method that uses the characteristics of column store tables is performed.

Output example
```
<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-TABLE SCAN(ADBUSER01.T1) -COLUMN STORE
     4   +-GROUPING -COLUMN
```
Explanation:

The grouping method that uses the characteristics of column store tables is performed for table T1.
GROUPING SET

Grouping processing is performed multiple times.

Output example
```
<<Tree View>>
     1 QUERY : 1
     2 SELECT STATEMENT
     3 |-DERIVED TABLE(##DRVTBL_0000000001)
     4 | |-TABLE SCAN(ADBUSER01.T1)
     5 | +-GLOBAL HASH GROUPING -GROUPING SET
     6 +-GLOBAL HASH GROUPING
```
Explanation:

Grouping processing is performed multiple times by global hash grouping.

Note

GROUPING SET (grouping set information) might be output if multiple DISTINCT set functions are specified with different arguments.

To Page Top

(14) HAVING clause specification

The following information is displayed:

HAVING

The HAVING clause is specified. This information might also be displayed if a derived table is expanded even though the HAVING clause is not specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-TABLE SCAN(ADBUSER01.T1)
     4   |-GLOBAL HASH GROUPING
     5   +-HAVING

Explanation:: The HAVING clause is specified in the SELECT statement.

To Page Top

(15) Sort processing

The following information is displayed:

SORTING {BYTE|ISO}
- BYTE: Sorts by bytecode.
- ISO: Sorts by sort code (ISO/IEC 14651:2011 compliance).
Sort processing is performed according to the ORDER BY clause.

Note that this information might not be displayed even though the ORDER BY clause is specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4   | |-TABLE SCAN(ADBUSER01.T1)
     5   | +-SORTING BYTE
     6   +-WORK TABLE SCAN(WORK TABLE 1)

Explanation:: Sort processing is performed according to the specified ORDER BY clause.

To Page Top

(16) Information about duplicate removal

The following information is displayed:

DISTINCT

This item indicates that duplicate removal will be performed.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2 SELECT STATEMENT
     3 |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4 | |-KEY SCAN(ADBUSER01.T1)
     5 | +-SORTING BYTE -DISTINCT
     6 +-WORK TABLE SCAN(WORK TABLE 1)

Explanation:: The item indicating that duplicate removal will be performed during sort processing is output.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2 SELECT STATEMENT
     3 |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4 | |-KEY SCAN(ADBUSER01.T1) -DISTINCT
     5 | +-SORTING BYTE -DISTINCT
     6 +-WORK TABLE SCAN(WORK TABLE 1)

Explanation:: Items indicating that duplicate removal will be performed during key scan processing and sort processing are included.

To Page Top

(17) SELECT deduplication method specification

The following information is displayed:

SPECIFIC

SELECT deduplication method specification is enabled.

For details about the SELECT deduplication method specification, see Specification format and rules for query specifications in the manual HADB SQL Reference.

Note that if DISTINCT described in (16) Information about duplicate removal is not output, SPECIFIC is not output even if the SELECT deduplication method specification is specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4   | |-TABLE SCAN(ADBUSER01.T1)
     5   | +-SORTING BYTE -SPECIFIC -DISTINCT
     6   +-WORK TABLE SCAN(WORK TABLE 1)

Explanation:: The SELECT deduplication method specification specified in the SELECT statement is enabled.

To Page Top

(18) LIMIT clause specification

The following information is displayed:

LIMIT [{offset|? PARAMETER},]{row_count|? PARAMETER}
- offset
  
  A LIMIT clause specifying the offset for the first row to be returned is specified.
  
  If the literal 0 is specified for the offset row count, no offset for the first row to be returned is displayed.
- row_count
  
  A LIMIT clause specifying the maximum number of rows to be returned is specified.
- ? PARAMETER
  
  A LIMIT clause containing dynamic parameters for both or either of the offset row count and limit row count is specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4   | |-TABLE SCAN(ADBUSER01.T1)
     5   | |-SORTING BYTE
     6   | +-LIMIT ? PARAMETER,5
     7   |-WORK TABLE SCAN(WORK TABLE 1)
     8   +-LIMIT ? PARAMETER,5

Explanation:: A LIMIT clause containing a dynamic parameter for the offset row count and 5 for the limit row count is specified.

To Page Top

(19) Specification of window functions

The following information is displayed:

WINDOW

A window function is specified.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-CREATE LOCAL WORK TABLE(WORK TABLE 1)
     4   | |-TABLE SCAN(ADBUSER01.T1)
     5   | +-SORTING BYTE
     6   |-WORK TABLE SCAN(WORK TABLE 1)
     7   +-WINDOW

Explanation:: A window function is executed according to its specification.

To Page Top

(20) Table retrieval method

One of the following is displayed:

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

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-INDEX SCAN(ADBUSER01.T1)

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

To Page Top

(21) Table-data storage format

The following information is displayed:

COLUMN STORE

The table-data storage format is column store format.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(ADBUSER01.T1) -COLUMN STORE

Explanation:: The table-data storage format of table T1 is column store format.

To Page Top

(22) Sequential execution

The following information is displayed:

ORDER

Sequential execution, not out-of-order execution, is applied.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-NESTED LOOP JOIN
     4     |-TABLE SCAN(ADBUSER01.T2)
     5     +-INDEX SCAN(ADBUSER01.T1) -ORDER

Explanation:: In the table T1 retrieval processing, an index scan is performed using the sequential execution method.

To Page Top

(23) Index specification

One of the following is displayed:

SPECIFIC

The index specification is enabled.
SPECIFIC DISABLED

The index specification is disabled.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-INDEX SCAN(ADBUSER01.T1) -SPECIFIC

Explanation:: An index scan is performed in the table T1 retrieval processing with an index specification specified in the SELECT statement enabled.

To Page Top

(24) Collecting cost information

The following information is displayed:

USING COST

Cost information is collected for a table or index.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-TABLE SCAN(ADBUSER01.T3) -USING COST

Explanation:: Cost information was collected for the table T3.

To Page Top

(25) Work table scan

The following information is displayed:

WORK TABLE SCAN(WORK TABLE work-table-number)

A work table is scanned. A unique work table number is assigned to each work table.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   |-SUBQUERY
     4   | +-CREATE GLOBAL WORK TABLE(WORK TABLE 1)
     5   |   |-QUERY SCAN(QUERY 2)
     6   |   +-SORTING BYTE
     7   +-INDEX SCAN(ADBUSER01.T1)
     8     +-SUBQUERY
     9       +-WORK TABLE SCAN(WORK TABLE 1)
    10 
    11 QUERY : 2
    12   SUBQUERY
    13   +-TABLE SCAN(ADBUSER01.T2)

Explanation:: A work table is scanned in the subquery processing.

Note that you might encounter circumstances in which the row IDs of a table specified in a FROM clause are stored in a work table created when executing an SQL statement that specifies an ORDER BY clause. In this case, data might be retrieved from data pages using these row IDs immediately after the work table is retrieved. For details about the purposes and columns of work tables, see 5.10 Considerations when executing an SQL statement that creates work tables.

To Page Top

(26) Query scan

The following information is displayed:

QUERY SCAN(QUERY query-tree-number)

A query scan is performed.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-SUBQUERY HASH
     4     |-QUERY SCAN(QUERY 2)
     5     +-TABLE SCAN(ADBUSER01.T1)
     6 
     7 QUERY : 2
     8   SUBQUERY HASH
     9   +-TABLE SCAN(ADBUSER01.T2)

Explanation:

A query scan is performed.

In this example, the query scan indicated as QUERY : 2 displayed in tree row number 7 is performed.

Example of the executed SELECT statement

SELECT * FROM "T1" WHERE "C1"=(SELECT "C2" FROM "T2" WHERE "C1"="T1"."C1")

To Page Top

(27) Table joining methods

One of the following is displayed:

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 HASH JOIN is followed by FILTER, a hash filter is applied during hash join.

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 (NESTED LOOP JOIN)

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-NESTED LOOP JOIN
     4     |-TABLE SCAN(ADBUSER01.T2)
     5     +-INDEX SCAN(ADBUSER01.T1) -ORDER

Explanation:

A nested loop join is performed to join tables T1 and T2.
If the table joining method is nested loop join, the information is displayed under NESTED LOOP JOIN in order, beginning with the outer table. In this example, the information in tree row number 4 is for the outer table and the information in tree row number 5 is for the inner table.

Output example (HASH JOIN)

<<Tree View>>

     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-HASH JOIN -FILTER
     4     |-TABLE SCAN(ADBUSER01.T1)
     5     +-TABLE SCAN(ADBUSER01.T2)

Explanation:

A hash join is performed to join tables T1 and T2.
If the table joining method is hash join, information for the outer table and the inner table for hash join is displayed in this order under HASH JOIN. In this example, the information in tree row number 4 is for the outer table and the information in tree row number 5 is for the inner table.
Because HASH JOIN is followed by FILTER in tree row number 3, a hash filter is applied during hash join.

To Page Top

(28) Join method specification

Either of the following is displayed:

SPECIFIC

Join method specification is enabled.
SPECIFIC DISABLED

Join method specification is disabled.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-HASH JOIN -SPECIFIC
     4     |-TABLE SCAN(ADBUSER01.T1(A))
     5     +-TABLE SCAN(ADBUSER01.T2(B))

Explanation:: The join method specification specified in the SELECT statement is enabled, and a hash join was executed in the join processing for the table.

To Page Top

(29) Scan information for table value constructors

The following information is displayed:

TABLE VALUE CONSTRUCTOR SCAN

Table value constructors are scanned.

Output example

<<Tree View>>
 
     1 QUERY : 1
     2   SELECT STATEMENT
     3   +-NESTED LOOP JOIN
     4     |-TABLE SCAN(ADBUSER01.T1)
     5     +-DERIVED TABLE(DT)
     6     +-TABLE VALUE CONSTRUCTOR SCAN

Explanation:: Table value constructors are scanned.

To Page Top