17.5.3 Join processing information

Organization of this subsection
(1) Join retrieval executed in a HiRDB single server configuration or HiRDB parallel server configuration (SELECT-APSL not used)
(2) Join retrieval executed in a HiRDB parallel server configuration (SELECT-APSL used)

(1) Join retrieval executed in a HiRDB single server configuration or HiRDB parallel server configuration (SELECT-APSL not used)

JOIN
 # Join ID     :aa...a
   L Join ID   : bb...b ff...f{BB...B}
   L Table     : cc...c(cc...c) dddddddddd(dd...d) ff...f{BB...B} {AA...A}
   L SubQ ID   : ee...e ff...f{BB...B}
   R Join ID   : gg...g kk...k{DD...D}
   R Table     : hh...h(hh...h) iiiiiiiiii(ii...i) kk...k{DD...D} {CC...C}
   R SubQ ID   : jj...j kk...k{DD...D}
   R SubQEX ID : tt...t kk...k{DD...D}
   JoinCnd     :{EE...E}
   Join Type   :ll...l-CLM mm...m(nn...n) oo...o-CLM pp...p(qq...q)  [rr...r]
   FLT Server  :ss...s
   RowCnd      :{FF...F}
   RowCndAfter :{GG...G}
   IfThenCndOn :{HH...H}
   IfThenCnd   :{II...I}

Explanation
aa...a
Displays the join processing ID.
The utility assigns this number to each join processing. If join processing is executed more than once, information about each join processing is separated by this line.
bb...b
If the left-hand term is another join result, the utility displays the corresponding join processing ID.
If the left-hand term is not a join result, the utility does not display this line.
cc...c(cc...c)
If the left-hand term is a table, the utility displays the table name (correlation name). The correlation name is not displayed if it is not used. Depending on the type of table, the following information is displayed:
  • Name of the query for a derived table in the WITH clause
  • Name of the view table (correlation name) for a view table
  • (NO NAME) or (NO NAME)(correlation-name) for a derived table that was specified in the FROM clause
If the join target is not a table, this line is not displayed.
dddddddddd(dd...d)
If the left-hand term is a table, the utility displays the table ID in hexadecimal and in decimal (the latter in parentheses).
If the left-hand term is not a table, the utility does not display this line.
ee...e
If the left-hand join target is a subquery, the utility displays the subquery ID.
If the join target is not a subquery, this line is not displayed.
ff...f{BB...B}
Displays information if a work table is to be created before join processing. 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).
gg...g
If the right-hand term is another join result, the utility displays the corresponding join processing ID.
If the right-hand term is not a join result, the utility does not display this line.
hh...h(hh...h)
If the right-hand term is a table, the utility displays the table name (correlation name).The correlation name is not displayed if it is not used. Depending on the type of table, the following information is displayed:
  • Name of the query for a derived table in the WITH clause
  • Name of the view table (correlation name) for a view table
  • (NO NAME) or (NO NAME)(correlation-name) for a derived table that was specified in the FROM clause
If the right-hand term is not a table, the utility does not display this line.
iiiiiiiiii(ii...i)
If the right-hand term is a table, the utility displays the table ID in hexadecimal and in decimal (the latter in parentheses).
If the right-hand term is not a table, the utility does not display this line.
jj...j
If the right-hand join target is a subquery that includes no set operation, the utility displays the subquery ID. If the join target is not a subquery that includes no set operation, this line is not displayed.
kk...k{DD...D}
Displays information if a work table is to be created before executing join processing. 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).
ll...l-CLM mm...m(nn...n)
Displays the join method:
ll...l
Displays the number of join conditions (number of columns) used for join processing.
mm...m
Displays the type of join method. For details about types of join methods, see 17.5.5 Types of join methods.
nn...n
Displays the execution type of join processing:
  • INNER
    Inner join. The utility evaluates the retrieval condition and returns the join result if the condition is true.
  • LEFT OUTER[(R)]
    Left outer join. When RIGHT [OUTER] JOIN is specified in the SQL statement, the utility displays (R) (when LEFT [OUTER] JOIN is specified, the utility displays nothing). The SQL optimization process converts a right outer join to the equivalent left outer join, and then performs the left outer join.
    The utility evaluates the ON retrieval condition and returns the join result if the condition is true. If none of the inner table's rows is true for the outer table's row, the utility fills the null value in the inner table's column and returns the join result.
  • EXIST
    The utility evaluates the retrieval condition and returns the outer table's row if at least one of the rows in the inner table is true for the outer table's row.
  • NOT EXIST
    The utility evaluates the retrieval condition and returns the outer table's row if none of the rows in the inner table is true for the outer table's row.
  • ALL
    The utility evaluates the retrieval condition and returns the outer table's row if all rows in the inner table are true for the outer table's row.
  • VALUE
    The utility evaluates the retrieval condition and returns the outer table's row if only one of the rows in the inner table is true for the outer table's row. If more than one row in the inner table is true, an error results.
oo...o-CLM pp...p(qq...q)
Displays the method for transferring to the server used for join processing:
oo...o
Displays the number of columns used for transfer.
pp...p
Displays the type of transfer method. For details about types of transfer methods, see 17.5.7 Types of transfer methods.
qq...q
Displays the transfer direction:
  • FROM L TO R
    Transfers from left to right to achieve join processing.
  • FROM R TO L
    Transfers from right to left to achieve join processing.
  • TO FLT
    Transfers both left- and right-hand terms to the floating server to achieve join processing.
rr...r
If the SQL optimization specification is used for the join method, this item displays whether or not the specification takes effect. For details about the SQL optimization specification, see the manual HiRDB Version 9 SQL Reference.
AS SPECIFIED: Takes effect
SPECIFICATION IGNORED: Ignored
ss...s
Displays the number of floating servers requested for sort processing in a HiRDB parallel server configuration. During the retrieval, the utility uses all available floating servers within the number requested. If the join method is NESTED LOOPS JOIN, this value is 0. This information is not displayed in a HiRDB single server configuration.
tt...t
If the right-hand join target is a subquery that includes a set operation, the utility displays the subquery ID.
If the join target is not a subquery containing a set operation, this line is not displayed.

(2) Join retrieval executed in a HiRDB parallel server configuration (SELECT-APSL used)

JOIN
 # Join ID     :aa...a
   L Join ID   : bb...b ee...e{AA...A}
   L Table     : cc...c(cc...c) dddddddddd(dd...d)  ee...e{AA...A}
   R Join ID   : ff...f  ii...i{BB...B}
   R Table     : gg...g(gg...g) hhhhhhhhhh(hh...h)  ii...i{BB...B}
   JoinCnd     :{CC...C}
   Join Type   :SELECT-APSL
         Table Name   :jj...j(jj...j) kkkkkkkkkk(kk...k)
         Column ID    :llllllllll(ll...l)
         Predicate    :mm...m
         Threshold    :nn...n
      [1] oo...o-CLM pp...p(qq...q)  rr...r-CLM ss...s(tt...t) ........1
          FLT Server  :uu...u
          IfThenCnd   :{DD...D}
      [2] oo...o-CLM pp...p(qq...q)  rr...r-CLM ss...s(tt...t) ........2
          FLT Server  :uu...u
          IfThenCnd   :{DD...D}

Explanation

1 and 2 indicate the first candidate and second candidate, respectively, for the join method.

aa...a
Displays the join processing ID.
The utility assigns this number to each join processing. If join processing is executed more than once, information about each join processing is separated by this line.
bb...b
If the left-hand term is another join result, the utility displays the corresponding join processing ID.
If the left-hand term is not a join result, the utility does not display this line.
cc...c(cc...c)
If the left-hand term is a table, the utility displays the table name (correlation name).
The correlation name is not displayed if it is not used. Depending on the type of table, the following information is displayed:
  • Name of the query for a derived table in the WITH clause
  • Name of the view table (correlation name) for a view table
  • (NO NAME) or (NO NAME)(correlation-name) for a derived table that was specified in the FROM clause
If the left-hand term is not a table, the utility does not display this line.
dddddddddd(dd...d)
If the left-hand term is a table, the utility displays the table ID in hexadecimal and in decimal (the latter in parentheses).
If the left-hand term is not a table, the utility does not display this line.
ee...e{AA...A}
Displays information if a work table is to be created before join processing. 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).
ff...f
If the right-hand term is another join result, the utility displays the corresponding join processing ID.
If the right-hand term is not a join result, the utility does not display this line.
gg...g(gg...g)
If the right-hand term is a table, the utility displays the table name (correlation name).
The correlation name is not displayed if it is not used. Depending on the type of table, the following information is displayed:
  • Name of the query for a derived table in the WITH clause
  • Name of the view table (correlation name) for a view table
  • (NO NAME) or (NO NAME)(correlation-name) for a derived table that was specified in the FROM clause
If the right-hand term is not a table, the utility does not display this line.
hhhhhhhhhh(hh...h)
If the right-hand term is a table, displays the table ID in hexadecimal and in decimal (the latter in parentheses).
If the right-hand term is not a table, the utility does not display this line.
ii...i{BB...B}
Displays information if a work table is to be created before join processing. 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).
jj...j(jj...j)
Displays the table name (correlation name) for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.
kkkkkkkkkk(kk...k)
Displays in hexadecimal and in decimal (the latter in parentheses) the table ID for the column used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.
llllllllll(ll...l)
Displays in hexadecimal and in decimal (the latter in parentheses) the column ID used in the predicate that is subject to calculation of the SQL run-time hit rate when selecting an SQL object.
mm...m
Displays the predicate subject to calculation of the SQL run-time hit rate when selecting an SQL object.
nn...n
Displays the reference hit rate for selecting an SQL object.
If the hit rate obtained from calculation is less than this value, the first candidate is used for the SQL object; if it is equal to or greater than this value, the second candidate is used for the SQL object.
oo...o-CLM pp...p(qq...q)
Displays the join method used:
oo...o
Displays the number of join conditions (number of columns) used for join processing.
pp...p
Displays the type of join method. For details about types of join methods, see 17.5.5 Types of join methods.
qq...q
Displays the execution type of join processing:
INNER: Inner join. The utility evaluates the retrieval condition and returns the join result if the condition is true.
rr...r-CLM ss...s(tt...t)
Displays the method for transferring to the server used for join processing:
rr...r
Displays the number of columns used for transfer.
ss...s
Displays the type of transfer method. For details about types of transfer methods, see 17.5.7 Types of transfer methods.
tt...t
Displays the transfer direction:
  • FROM L TO R
    Transfers from left to right to achieve join processing.
  • FROM R TO L
    Transfers from right to left to achieve join processing.
  • TO FLT
    Transfers both left- and right-hand terms to the floating server to achieve join processing.
uu...u
Displays the number of floating servers requested for sort processing in a HiRDB parallel server configuration. During the retrieval, the utility uses all available floating servers within the number requested. If the join method is NESTED LOOPS JOIN, this value is 0. This information is not displayed for a HiRDB single server configuration.