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>

Description

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.

The following example illustrates an index ORing access request:

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>

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.