Forming table references in optimization guidelines

The term table reference is used to mean any table, view, table expression, or the table which an alias references in an SQL statement or view definition. An optimization guideline can identify a table reference using either its exposed name in the original statement or the unique correlation name that is associated with the table reference in the optimized statement.

Extended names, which are sequences of exposed names, help to uniquely identify table references that are embedded in views. An alias name cannot be used as a table reference in an optimization guideline, in such a case any guideline targeting the table reference will be ignored. Optimization guidelines that identify exposed or extended names that are not unique within the context of the entire statement are considered ambiguous and are not applied. Moreover, if more than one optimization guideline identifies the same table reference, all optimization guidelines identifying that table reference are considered conflicting and are not applied. The potential for query transformations makes it impossible to guarantee that an exposed or extended name will still exist during optimization; therefore, any guideline that identifies such table references is ignored.

Index names are specified by using the INDEX or IXNAME attribute on some optimization guideline elements. Index names must be unqualified.

Using exposed names in the original statement to identify table references

A table reference is identified by using the exposed name of the table. The exposed name is specified in the same way that a table would be qualified in an SQL statement.

The rules for specifying SQL identifiers also apply to the TABLE attribute value of an optimization guideline. The TABLE attribute value is compared to each exposed name in the statement. Only a single match is permitted in this Db2® release. If the TABLE attribute value is schema-qualified, it matches any equivalent exposed qualified table name. If the TABLE attribute value is unqualified, it matches any equivalent correlation name or exposed table name. The TABLE attribute value is therefore considered to be implicitly qualified by the default schema that is in effect for the statement. These concepts are illustrated by the following example. Assume that the statement is optimized using the default schema Samp.

   select s_name, s_address, s_phone, s_comment
     from parts, suppliers, partsupp ps
     where p_partkey = ps.ps_partkey and
       s.s_suppkey = ps.ps_suppkey and
       p_size = 39 and
       p_type = 'BRASS'

TABLE attribute values that identify a table reference in the statement include '"Samp".PARTS', 'PARTS', 'Parts' (because the identifier is not delimited, it is converted to uppercase characters). TABLE attribute values that fail to identify a table reference in the statement include '"Samp2".SUPPLIERS', 'PARTSUPP' (not an exposed name), and 'Samp.PARTS' (the identifier Samp must be delimited; otherwise, it is converted to uppercase characters).

The exposed name can be used to target any table reference in the original statement, view, SQL function, or trigger.

Using exposed names in the original statement to identify table references in views

Optimization guidelines can use extended syntax to identify table references that are embedded in views, as shown in the following example:

   create view "Rick".v1 as
     (select * from employee a where salary > 50000)

   create view "Gustavo".v2 as
     (select * from "Rick".v1
        where deptno in ('52', '53', '54')

   select * from "Gustavo".v2 a
     where v2.hire_date > '01/01/2004'

   <OPTGUIDELINES>
     <IXSCAN TABLE='A/"Rick".V1/A'/>
   </OPTGUIDELINES>

The IXSCAN access request element specifies that an index scan is to be used for the EMPLOYEE table reference that is embedded in the views "Gustavo".V2 and "Rick".V1. The extended syntax for identifying table references in views is a series of exposed names separated by a slash character. The value of the TABLE attribute A/"Rick".V1/A illustrates the extended syntax. The last exposed name in the sequence (A) identifies the table reference that is a target of the optimization guideline. The first exposed name in the sequence (A) identifies the view that is directly referenced in the original statement. The exposed name or names in the middle ("Rick".V1) pertain to the view references along the path from the direct view reference to the target table reference. The rules for referring to exposed names from optimization guidelines, described in the previous section, apply to each step of the extended syntax.

Had the exposed name of the EMPLOYEE table reference in the view been unique with respect to all tables that are referenced either directly or indirectly by the statement, the extended name syntax would not be necessary.

Extended syntax can be used to target any table reference in the original statement, SQL function, or trigger.

Identifying table references using correlation names in the optimized statement

An optimization guideline can also identify a table reference using the unique correlation names that are associated with the table reference in the optimized statement. The optimized statement is a semantically equivalent version of the original statement, as determined during the query rewrite phase of optimization. The optimized statement can be retrieved from the explain tables. The TABID attribute of an optimization guideline is used to identify table references in the optimized statement.

Original statement:

   select s.s_name, s.s_address, s.s_phone, s.s_comment
     from sm_samp.parts p, sm_samp.suppliers s, sm_samp.partsupp ps
     where p_partkey = ps.ps_partkey and
       s.s_suppkey = ps.ps_suppkey and
       p.p_size = 39 and
       p.p_type = 'BRASS' and
       s.s_nation in ('MOROCCO', 'SPAIN') and
       ps.ps_supplycost = (select min(ps1.ps_supplycost)
                             from sm_samp.partsupp ps1, sm_samp.suppliers s1
                             where p.p_partkey = ps1.ps_partkey and
                               s1.s_suppkey = ps1.ps_suppkey and
                               s1.s_nation = s.s_nation)

   <OPTGUIDELINES>
     <HSJOIN>
       <TBSCAN TABLE='S1'/>
       <IXSCAN TABID='Q2'/>
     </HSJOIN>
   </OPTGUIDELINES>

Optimized statement:

   select q6.s_name as "S_NAME", q6.s_address as "S_ADDRESS",
       q6.s_phone as "S_PHONE", q6.s_comment as "S_COMMENT"
     from (select min(q4.$c0)
             from (select q2.ps_supplycost
                     from sm_samp.suppliers as q1, sm_samp.partsupp as q2
                     where q1.s_nation = 'MOROCCO' and
                       q1.s_suppkey = q2.ps_suppkey and
                       q7.p_partkey = q2.ps_partkey
                     ) as q3
             ) as q4, sm_samp.partsupp as q5, sm_samp.suppliers as q6,
                 sm_samp.parts as q7
     where p_size = 39 and
       q5.ps_supplycost = q4.$c0 and
       q6.s_nation in ('MOROCCO', 'SPAIN') and
       q7.p_type = 'BRASS' and
       q6.s_suppkey = q5.ps_suppkey and
       q7.p_partkey = q5.ps_partkey

This optimization guideline shows a hash join request, where the SUPPLIERS table in the nested subselect is the outer table, as specified by the TBSCAN access request element, and where the PARTSUPP table in the nested subselect is the inner table, as specified by the IXSCAN access request element. The TBSCAN access request element uses the TABLE attribute to identify the SUPPLIERS table reference using the corresponding exposed name in the original statement. The IXSCAN access request element, on the other hand, uses the TABID attribute to identify the PARTSUPP table reference using the unique correlation name that is associated with that table reference in the optimized statement.

If a single optimization guideline specifies both the TABLE and TABID attributes, they must identify the same table reference, or the optimization guideline is ignored.

Note: There is currently no guarantee that correlation names in the optimized statement will be stable when upgrading to a new release of the Db2 product.

Ambiguous table references

An optimization guideline is considered invalid and is not applied if it matches multiple exposed or extended names.

   create view v1 as
     (select * from employee 
        where salary > (select avg(salary) from employee)

   select * from v1
     where deptno in ('M62', 'M63')

   <OPTGUIDE>
     <IXSCAN TABLE='V1/EMPLOYEE'/>
   </OPTGUIDE>

The optimizer considers the IXSCAN access request ambiguous, because the exposed name EMPLOYEE is not unique within the definition of view V1.

To eliminate the ambiguity, the view can be rewritten to use unique correlation names, or the TABID attribute can be used. Table references that are identified by the TABID attribute are never ambiguous, because all correlation names in the optimized statement are unique.

Conflicting optimization guidelines

Multiple optimization guidelines cannot identify the same table reference.

For example:

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

Each of the IXSCAN elements references the "Samp".PARTS table in the main subselect.

When two or more guidelines refer to the same table, only the first is applied; all other guidelines are ignored, and an error is returned.

Only one INLIST2JOIN query rewrite request element at the predicate level per query can be enabled.The following example illustrates an unsupported query rewrite optimization guideline, where two IN-LIST predicates are enabled at the predicate level. Both guidelines are ignored, and a warning is returned.

   <OPTGUIDELINES>
     <INLIST2JOIN TABLE='P' COLUMN='P_SIZE'/>
	   <INLIST2JOIN TABLE='P' COLUMN='P_TYPE'/>
   </OPTGUIDELINES>