List prefetch access requests

The LPREFETCH access request element can be used to specify that the optimizer is to use a list prefetch index scan to access a local table. It is defined by the complex type listPrefetchType.

XML Schema

   <xs:complexType name="listPrefetchType">
      <xs:complexContent>
         <xs:extension base="accessType">
            <xs:attribute name="INDEX" type="xs:string" use="optional"/>
         </xs:extension>
      </xs:complexContent>
   </xs:complexType>

Description

The complex type listPrefetchType extends the abstract type accessType by adding an optional INDEX attribute. The INDEX attribute specifies the name of the index that is to be used to access the table.
  • If the list prefetch 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 list prefetch access method requires that at least one predicate is able to be indexed. If the list prefetch access method is not eligible because the required predicate does not exist, the access request is ignored and SQL0437W with reason code 13 is returned.
  • If the INDEX attribute is specified, it must identify an index defined on the table that is specified by the TABLE or TABID attribute. If the index does not exist, the access request is ignored and SQL0437W with reason code 13 is returned.
  • If the INDEX attribute is not specified, the optimizer chooses an index in a cost-based fashion. If no indexes are defined on the target table, the access request is ignored and SQL0437W with reason code 13 is returned.
The following guideline is an example of a list prefetch access request:
   <OPTGUIDELINES>
     <LPREFETCH TABLE='S1' INDEX='I_SNATION'/>
   </OPTGUIDELINES>