Join requests

The joinRequest group defines the set of valid join request elements. A join request specifies a method for joining two tables.

XML Schema

   <xs:group name="joinRequest">
      <xs:choice>
         <xs:element name="NLJOIN" type="nestedLoopJoinType"/>
         <xs:element name="HSJOIN" type="hashJoinType"/>
         <xs:element name="MSJOIN" type="mergeJoinType"/>
         <xs:element name="JOIN" type="anyJoinType"/>
      </xs:choice>
   </xs:group>

Description

  • NLJOIN, MSJOIN, and HSJOIN

    These elements correspond to the nested-loop, merge, and hash join methods, respectively.

  • JOIN

    This element, which causes the optimizer to choose the join method, can be used when the join order is not of primary concern.

All join request elements contain two sub-elements that represent the input tables of the join operation. Join requests can also specify an optional FIRST attribute.

The following guideline is an example of a join request:
   <OPTGUIDELINES>
     <HSJOIN>
       <ACCESS TABLE='S1'/>
       <IXSCAN TABLE='PS1'/>
     </HSJOIN>
   </OPTGUIDELINES>
The nesting order ultimately determines the join order. The following example illustrates how larger join requests can be constructed from smaller join requests:
   <OPTGUIDELINES>
     <MSJOIN>
       <NLJOIN>
         <IXSCAN TABLE='"Samp".Parts'/>
         <IXSCAN TABLE="PS"/>
       </NLJOIN>
       <IXSCAN TABLE='S'/>
     </MSJOIN>
   </OPTGUIDELINES>