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>