Set operation information

 
 ----- QUERY EXPRESSION BODY ID : aa...a -----
 Query Exp Type : bb...b cc...c(cc...c)  dd...d{AA...A}
 Sub Query Type : ee...e{BB...B} [ff...f]
 Transfer Type  : gg...g-CLM hh...h
 Order by Mode  : ii...i
 FLT Server     : jj...j
 SetOpe Process : kk...k = [ll...l mm...m{CC...C}] nn...n [ll...l mm...m{CC...C}]
                     :

Explanation

aa...a: Displays the ID of the query expression.; The utility assigns a new number to each query expression including Set Operation. If the compiled SQL statement consists of multiple query expressions, information is separated by this line.

bb...b

Displays the type of query expression:

QUERY: Query

DERIVED TABLE: Derived table query when a query expression was specified in the FROM clause

SUBQUERY: Subquery

WITH: WITH clause query

VIEW: View definition query

FOREIGN: Query for accessing the foreign server

cc...c(cc...c)

One of the following, depending on the type of query expression:

Name of the WITH clause query (correlation name) for a WITH clause query expression
Name of the view table (correlation name) for a view definition query expression
(NO NAME) or (NO NAME)(correlation-name) for a query expression in the derived table that was specified in the FROM clause

dd...d{AA...A}: Displays information when a work table is to be created for the set operation result. This information is not displayed if no work table is created.; If work table creation does not accompany a sort operation, the utility displays LIST.; If work table creation accompanies a sort operation, the utility displays LIST(SORT).

ee...e

Displays the subquery execution method (for details, see Execution of subqueries with no external reference and Execution of subqueries with external reference in the HiRDB Version 8 UAP Development Guide):

WORK TABLE ATS SUBQ

The subquery execution method is work table ATS.

WORK TABLE SUBQ: The subquery execution method is work table.

ROW VALUE SUBQ: The subquery execution method is row value.

HASH SUBQ: The subquery execution method is hash.

NESTED LOOPS WORK TABLE SUBQ: The subquery execution method is nested loop work table.

NESTED LOOPS ROW VALUE SUBQ: The subquery execution method is nested loop row value.

ff...f

When the SQL optimization specification is used for the subquery execution method, displays whether or not the specification took effect (for details, see the manual HiRDB Version 8 SQL Reference):

AS SPECIFIED: SQL optimization specification was applied because the specification took effect.

SPECIFICATION IGNORED: SQL optimization specification was ignored because the specification did not take effect.

gg...g-CLM hh...h: Displays the method for transferring the result to the high-order query for a subquery, WITH clause query expression, view definition, or query expression created internally by HiRDB.; gg...g displays the number of rows used for transfer, and hh...h displays the type of transfer method. For details about the transfer method types, see 17.5.8 Types of transfer methods.

ii...i: Displays the ORDER BY processing method for the set operation. When ORDER BY is not specified, this line is not displayed. For details about the ORDER BY processing method, see 17.5.14 Types of ORDER BY processing methods.; For a HiRDB/Single Server, the ORDER BY processing method for queries is also displayed as query processing information.

jj...j: Displays the number of floating servers requested for the set operation. If no floating server is needed, 0 is displayed. This item is not displayed for a HiRDB/Single Server.

kk...k = [ll...l mm...m{CC...C}] nn...n [ll...l mm...m{CC...C}]

Displays the order of set operations. When multiple set operations are specified, this item consists of multiple lines.

kk...k: Displays the set operation number in the set operation results, in the format "LID(set-operation-number)".

ll...l: Displays "QID(query-ID)" if the query expression to be joined is a query specification. If a join result of multiple query specifications is the query expression to be joined, this item displays "LID(set-operation-number)".

mm...m{CC...C}: Displays information to create a work table before a set operation is executed. If no work table is created, this item is not displayed.; If a work table is created without sorting, LIST is displayed; if a work table is created with sorting, LIST(SORT) is displayed.; If the LIMIT clause is specified and only the first n items are obtained from the sorting results in memory without creating a work table, MEM(SUBSORT) is displayed.

nn...n: Displays the type of set operation ("UNION", "UNION ALL", "EXCEPT", or "EXCEPT ALL"). [ll...l mm...m{CC...C}] before and after nn...n indicate the query expressions to be joined.

The following is a display example of the order of set operations:

 
((SELECT C1 FROM T1   ...................query-ID:1
    UNION SELECT C1 FROM T2)   ..........query-ID:2
      UNION ALL SELECT C1 FROM T3)   ....query-ID:3

 
SetOpe Process : LID(1) = [QID(1) LIST(SORT) {...}] UNION [QID(2) LIST(SORT) {...}]
                 LID(2) = [LID(1) LIST{...}] UNION ALL [QID(3) LIST{...}]

17.5.2 Set operation information