Index ORing access requests
The IXOR access request element can be used to specify that the optimizer is to use the index ORing data access method to access a local table. It is defined by the complex type indexOringType.
XML Schema <xs:complexType name="indexOringType"> <xs:complexContent> <xs:extension base="accessType"/> </xs:complexContent> </xs:complexType>
The complex type indexOringType is a simple extension of the abstract type accessType. No new elements or attributes are added. If the index ORing access method is not in the search space that is in effect for the statement, the access request is ignored and SQL0437W with reason code 13 is returned. The optimizer chooses the predicates and indexes that are used in the index ORing operation in a cost-based fashion. The index ORing access method requires that at least one IN predicate is able to be indexed or that a predicate with terms is able to be indexed and connected by a logical OR operation. If index ORing is not eligible because the required predicate or indexes do not exist, the request is ignored and SQL0437W with reason code 13 is returned.
This index ORing access request specifies that an index ORing data access method is to be used to access the SUPPLIERS table that is referenced in the main subselect. The optimizer will choose the appropriate predicates and indexes for the index ORing operation in a cost-based fashion.
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 optimize for 1 row Optimization guideline: <OPTGUIDELINES> <IXOR TABLE='S'/> </OPTGUIDELINES>