Access types

Common aspects of the TBSCAN, IXSCAN, LPREFETCH, IXAND, IXOR, XISCAN, XANDOR, and ACCESS elements are defined by the abstract type accessType.

XML Schema

   <xs:complexType name="accessType" abstract="true">
      <xs:attribute name="TABLE" type="xs:string" use="optional"/>
      <xs:attribute name="TABID" type="xs:string" use="optional"/>
      <xs:attribute name="FIRST" type="xs:string" use="optional" fixed="TRUE"/>
      <xs:attribute name="SHARING" type="optionType" use="optional"
        default="ENABLE"/>
      <xs:attribute name="WRAPPING" type="optionType" use="optional"
        default="ENABLE"/>
      <xs:attribute name="THROTTLE" type="optionType" use="optional"/>
      <xs:attribute name="SHARESPEED" type="shareSpeed" use="optional"/>
   </xs:complexType>

   <xs:complexType name="extendedAccessType">
      <xs:complexContent>
         <xs:extension base="accessType">
            <xs:sequence minOccurs="0">
               <xs:element name="INDEX" type="indexType" minOccurs="2"
                 maxOccurs="unbounded"/>
            </xs:sequence>
            <xs:attribute name="INDEX" type="xs:string" use="optional"/>
            <xs:attribute name="TYPE" type="xs:string" use="optional"
              fixed="XMLINDEX"/>
            <xs:attribute name="ALLINDEXES" type="boolType" use="optional"
              fixed="TRUE"/>
         </xs:extension>
      </xs:complexContent>
   </xs:complexType>

Description

All access request elements extend the complex type accessType. Each such element must specify the target table reference using either the TABLE or TABID attribute. For information on how to form proper table references from an access request element, see Forming table references in optimization guidelines.

Access request elements can also specify an optional FIRST attribute. If the FIRST attribute is specified, it must have the value TRUE. Adding the FIRST attribute to an access request element indicates that the execution plan should include the specified table as the first table in the join sequence of the corresponding FROM clause. Only one access or join request per FROM clause can specify the FIRST attribute. If multiple access or join requests targeting tables of the same FROM clause specify the FIRST attribute, all but the first such request is ignored and a warning (SQL0437W with reason code 13) is returned.

New optimizer guidelines enable you to influence the compiler's scan sharing decisions. In cases where the compiler would have allowed sharing scans, wrapping scans, or throttling, specifying the appropriate guideline will prevent sharing scans, wrapping scans, or throttling. A sharing scan can be seen by other scans that are participating in scan sharing, and those scans can base certain decisions on that information. A wrapping scan is able to start at an arbitrary point in the table to take advantage of pages that are already in the buffer pool. A throttled scan has been delayed to increase the overall level of sharing.

Valid optionType values (for the SHARING, WRAPPING, and THROTTLE attributes) are DISABLE and ENABLE (the default). SHARING and WRAPPING cannot be enabled when the compiler chooses to disable them. Using ENABLE will have no effect in those cases. THROTTLE can be either enabled or disabled. Valid SHARESPEED values (to override the compiler's estimate of scan speed) are FAST and SLOW. The default is to allow the compiler to determine values, based on its estimate.

The only supported value for the TYPE attribute is XMLINDEX, which indicates to the optimizer that the table must be accessed using one of the XML index access methods, such as IXAND, IXOR, XANDOR, or XISCAN. If this attribute is not specified, the optimizer makes a cost-based decision when selecting an access plan for the specified table.

The optional INDEX attribute can be used to specify an index name.

The optional INDEX element can be used to specify two or more names of indexes as index elements. If the INDEX attribute and the INDEX element are both specified, the INDEX attribute is ignored.

The optional ALLINDEXES attribute, whose only supported value is TRUE, can only be specified if the TYPE attribute has a value of XMLINDEX. If the ALLINDEXES attribute is specified, the optimizer must use all applicable relational indexes and indexes over XML data to access the specified table, regardless of cost.