17.5.3 Query processing information

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

Explanation
aa...a
Displays the query ID.
The utility assigns a number to each query specification. If the compiled SQL query consists of multiple query specifications, the information is separated by this line.
For a query that includes access to a foreign table, the query ID for foreign server retrieval processing (prediction) information is the same as the query ID that is assigned to the SQL statement issued to the local HiRDB.
bb...b
Displays the type of query.
QUERY
Query
DERIVED TABLE
Derived table query (if a query expression was specified in the FROM clause)
SUBQUERY
Subquery
WITH
WITH clause query
VIEW
View definition query
DUMMY
Query internally created by HiRDB
FOREIGN
Query for accessing the foreign server
CONSTRAINT
Subquery in the referential constraint conditions
cc...c(cc...c)
Displays the following, depending on the query type:
  • Name of the WITH clause query (correlation name) for a WITH clause query specification
  • 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
  • If HiRDB created a query internally, the utility displays (DUMMY work-table-number) as the name of the temporary work table created internally to store the query results. The work table number is a 3-digit integer.
  • Name of the constraint for a subquery in the referential constraint conditions.
dd...d{AA...A}
Displays information if a work table is to be created to retrieve data and execute GROUP BY, ORDER BY, DISTINCT, set operation, or lock processing, etc., on the join result as required. This information is not displayed if no work table is created.
If multiple work tables are created due to a combination of operations, the tables are separated by the comma.
If work table creation does not accompany sorting, the utility displays LIST; if work table creation accompanies sorting, the utility displays LIST(SORT).
When the LIMIT clause is specified, if the first n items in the sorting result are to be obtained in memory without creating a work table, the utility displays MEM(SUBSORT).
ee...e{BB...B}
Displays the subquery execution method. For details about the subquery execution method, see 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
If the SQL optimization specification is used for the subquery execution method, this item displays whether or not the specification takes effect. For details about the SQL optimization specification, see the manual HiRDB Version 8 SQL Reference.
AS SPECIFIED: Takes effect
SPECIFICATION IGNORED: Ignored
gg...g-CLM hh...h
Displays the method for transferring the result to the high-order query for a subquery, WITH clause query, view definition query, set operation, or query 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 types of transfer methods, see 17.5.8 Types of transfer methods.
ii...i
Displays the ORDER BY processing method. The utility may execute ORDER BY processing implicitly even if the ORDER BY clause is not specified. For details about the ORDER BY processing method, see 17.5.14 Types of ORDER BY processing methods.
If ORDER BY processing does not take place, the utility does not display this line.
jj...j
Displays the grouping method (including implicit grouping). For details about the grouping methods, see 17.5.10 Types of grouping processing methods.
If grouping does not take place, the utility does not display this line.
kk...k
Displays the number of floating servers requested.
ll...l
Displays the purpose of using the floating servers.
During the retrieval, the utility uses all available floating servers within the number requested. This information is not displayed for a HiRDB/Single Server.
IMPLICIT GROUP BY
Purpose is to execute internal grouping for set function processing even if GROUP BY is not specified implicitly.
GROUP BY
Purpose is to execute grouping specified with GROUP BY.
DISTINCT
Purpose is to execute DISTINCT processing.
ORDER BY
Purpose is to execute ORDER BY processing.
FOR READ ONLY
Purpose is to process the FOR READ ONLY clause.
FOR UPDATE
Purpose is to process the FOR UPDATE clause.
FOR UPDATE OF
Purpose is to process FOR UPDATE OF column-name[,column-name]...
PRE-UPDATE DATA
Purpose is to allocate a floating server in order to reference pre-update data.
mm...m-CLM nn...n
Displays the method for transferring to the floating server. mm...m displays the number of rows used for transfer, and nn...n displays the type of transfer method. For details about the types of transfer methods, see 17.5.8 Types of transfer methods.