Any join requests

The JOIN join request element can be used to specify that the optimizer is to choose an appropriate method for joining two tables in a particular order.

Either table can be local or derived, as specified by an access request sub-element, or it can be the result of a join operation, as specified by a join request sub-element. A derived table is the result of another subselect. This join request element is defined by the complex type anyJoinType.

XML Schema

   <xs:complexType name="anyJoinType">
      <xs:complexContent>
         <xs:extension base="joinType"/>
      </xs:complexContent>
   </xs:complexType>

Description

The complex type anyJoinType is a simple extension of the abstract type joinType. No new elements or attributes are added.

The following example illustrates the use of the JOIN join request element to force a particular join order for a set of tables:
SQL statement:

  select s.s_name, s.s_address, s.s_phone, s.s_comment
     from "Samp".parts, "Samp".suppliers s, "Samp".partsupp ps
     where p_partkey = ps.ps_partkey and
       s.s_suppkey = ps.ps_suppkey and
       p_size = 39 and
       p_type = 'BRASS' and
       s.s_nation in ('MOROCCO', 'SPAIN') and
       ps.ps_supplycost = (select min(ps1.ps_supplycost)
                             from "Samp".partsupp ps1, "Samp".suppliers s1
                             where "Samp".parts.p_partkey = ps1.ps_partkey and
                               s1.s_suppkey = ps1.ps_suppkey and
                               s1.s_nation = s.s_nation)
     order by s.s_name

Optimization guideline:

   <OPTGUIDELINES>
     <JOIN>
       <JOIN>
         <ACCESS TABLE='"Samp".PARTS'/>
         <ACCESS TABLE='S'/>
       </JOIN>
       <ACCESS TABLE='PS'>
     </JOIN>
   </OPTGUIDELINES>
The JOIN join request elements specify that the PARTS table in the main subselect is to be joined with the SUPPLIERS table, and that this result is to be joined to the PARTSUPP table. The optimizer will choose the join methods for this particular sequence of joins in a cost-based fashion.