Plan optimization guidelines

Plan optimization guidelines are applied during the cost-based phase of optimization, where access methods, join methods, join order, and other details of the execution plan for the statement are determined.

Plan optimization guidelines need not specify all aspects of an execution plan. Unspecified aspects of the execution plan are determined by the optimizer in a cost-based fashion.

There are two categories of plan optimization guidelines:

accessRequest – An access request specifies an access method for satisfying a table reference in a statement.
joinRequest – A join request specifies a method and sequence for performing a join operation. Join requests are composed of access or other join requests.

Access request optimization guidelines correspond to the optimizer's data access methods, such as table scan, index scan, and list prefetch. Join request guidelines correspond to the optimizer's join methods, such as nested-loop join, hash join, and merge join. Each access request and join request is represented by a corresponding access request element and join request element in the statement optimization guideline schema.

The following example illustrates an index scan access request, as represented by the IXSCAN access request element. This particular request specifies that the optimizer is to use the I_SUPPKEY index to access the SUPPLIERS table in the main subselect of the statement. The optional INDEX attribute identifies the desired index. The TABLE attribute identifies the table reference to which the access request is applied. A TABLE attribute must identify the target table reference using its exposed name, which in this example is the correlation name S.

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>
     <IXSCAN TABLE='S' INDEX='I_SUPPKEY'/>
   </OPTGUIDELINES>

The following index scan access request element specifies that the optimizer is to use index access to the PARTS table in the main subselect of the statement. The optimizer will choose the index in a cost-based fashion, because there is no INDEX attribute. The TABLE attribute uses the qualified table name to refer to the target table reference, because there is no associated correlation name.

   <OPTGUIDELINES>
     <IXSCAN TABLE='"Samp".PARTS'/>
   </OPTGUIDELINES>

The following list prefetch access request is represented by the LPREFETCH access request element. This particular request specifies that the optimizer is to use the I_SNATION index to access the SUPPLIERS table in the nested subselect of the statement. The TABLE attribute uses the correlation name S1, because that is the exposed name identifying the SUPPLIERS table reference in the nested subselect.

   <OPTGUIDELINES>
     <LPREFETCH TABLE='S1' INDEX='I_SNATION'/>
   </OPTGUIDELINES>

The following index scan access request element specifies that the optimizer is to use the I_SNAME index to access the SUPPLIERS table in the main subselect. The FIRST attribute specifies that this table is to be the first table that is accessed in the join sequence chosen for the corresponding FROM clause. The FIRST attribute can be added to any access or join request; however, there can be at most one access or join request with the FIRST attribute referring to tables in the same FROM clause.

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
       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 guidelines:

   <OPTGUIDELINES>
     <IXSCAN TABLE='S' INDEX='I_SNAME' FIRST='TRUE'/>
   </OPTGUIDELINES>

The following example illustrates how multiple access requests are passed in a single statement optimization guideline. The TBSCAN access request element represents a table scan access request. This particular request specifies that the SUPPLIERS table in the nested subselect is to be accessed using a full table scan. The LPREFETCH access request element specifies that the optimizer is to use the I_SUPPKEY index during list prefetch index access to the SUPPLIERS table in the main subselect.

   <OPTGUIDELINES>
     <TBSCAN TABLE='S1'/>
     <LPREFETCH TABLE='S' INDEX='I_SUPPKEY'/>
   </OPTGUIDELINES>

The following example illustrates a nested-loop join request, as represented by the NLJOIN join request element. In general, a join request element contains two child elements. The first child element represents the desired outer input to the join operation, and the second child element represents the desired inner input to the join operation. The child elements can be access requests, other join requests, or combinations of access and join requests. In this example, the first IXSCAN access request element specifies that the PARTS table in the main subselect is to be the outer table of the join operation. It also specifies that PARTS table access be performed using an index scan. The second IXSCAN access request element specifies that the PARTSUPP table in the main subselect is to be the inner table of the join operation. It, too, specifies that the table is to be accessed using an index scan.

   <OPTGUIDELINES>
     <NLJOIN>
       <IXSCAN TABLE='"Samp".Parts'/>
       <IXSCAN TABLE="PS"/>
     </NLJOIN>
   </OPTGUIDELINES>

The following example illustrates a hash join request, as represented by the HSJOIN join request element. The ACCESS access request element specifies that the SUPPLIERS table in the nested subselect is to be the outer table of the join operation. This access request element is useful in cases where specifying the join order is the primary objective. The IXSCAN access request element specifies that the PARTSUPP table in the nested subselect is to be the inner table of the join operation, and that the optimizer is to choose an index scan to access that table.

   <OPTGUIDELINES>
     <HSJOIN>
       <ACCESS TABLE='S1'/>
       <IXSCAN TABLE='PS1'/>
     </HSJOIN>
   </OPTGUIDELINES>

The following example illustrates how larger join requests can be constructed by nesting join requests. The example includes a merge join request, as represented by the MSJOIN join request element. The outer input of the join operation is the result of joining the PARTS and PARTSUPP tables of the main subselect, as represented by the NLJOIN join request element. The inner input of the join request element is the SUPPLIERS table in the main subselect, as represented by the IXSCAN access request element.

   <OPTGUIDELINES>
     <MSJOIN>
       <NLJOIN>
         <IXSCAN TABLE='"Samp".Parts'/>
         <IXSCAN TABLE="PS"/>
       </NLJOIN>
       <IXSCAN TABLE='S'/>
     </MSJOIN>
   </OPTGUIDELINES>

If a join request is to be valid, all access request elements that are nested either directly or indirectly inside of it must reference tables in the same FROM clause of the optimized statement.

Optimization guidelines for MQT matching

Users can override the optimizer's decision and force it to choose specific materialized query tables (MQTs) with the MQTENFORCE element. The MQTENFORCE element, can be specified at both the global and statement profile level, is used with one of the following attributes:

NAME

specifies the partial or fully qualified name of MQT to choose

TYPE

specifies a group of MQTs by their types. Possible values are:

NORMAL: all non-replicated MQTs
REPLICATED: all replicated MQTs
ALL: all MQTs

The following example illustrates an example of a guideline that enforces all replicated MQTs, as well as, the SAMP.PARTSMQT:

	<OPTGUIDELINES>
		<MQTENFORCE NAME='SAMP.PARTSMQT'/>
		<MQTENFORCE TYPE='REPLICATED'/>
	</OPTGUIDELINES>

Note: If you specify more than one attribute at a time, only the first one will be used. So in the following example

<MQTENFORCE NAME='SAMP.PARTSMQT' TYPE='REPLICATED'/>

Only PARTSMQT MQT will be enforced