Query rewrite guidelines can be used to affect the transformations that are considered during the query rewrite optimization phase, which transforms the original statement into a semantically equivalent optimized statement.
The optimal execution plan for the optimized statement is determined during the plan optimization phase. Consequently, query rewrite optimization guidelines can affect the applicability of plan optimization guidelines.
Query rewrite optimization guidelines are not always applicable. Query rewrite rules are enforced one at a time. Consequently, query rewrite rules that are enforced before a subsequent rule can affect the query rewrite optimization guideline that is associated with that rule. The environment configuration can affect the behavior of some rewrite rules which, in turn, affects the applicability of a query rewrite optimization guideline to a specific rule.
To get the same results each time, query rewrite rules have certain conditions that are applied before the rules are enforced. If the conditions that are associated with a rule are not satisfied when the query rewrite component attempts to apply the rule to the query, the query rewrite optimization guideline for the rule is ignored. If the query rewrite optimization guideline is not applicable, and the guideline is an enabling guideline, SQL0437W with reason code 13 is returned. If the query rewrite optimization guideline is not applicable, and the guideline is a disabling guideline, no message is returned. The query rewrite rule is not applied in this case, because the rule is treated as if it were disabled.
Query rewrite optimization guidelines can be divided into two categories: statement-level guidelines and predicate-level guidelines. All of the query rewrite optimization guidelines support the statement-level category. Only INLIST2JOIN supports the predicate-level category. The statement-level query rewrite optimization guidelines apply to the entire query. The predicate-level query rewrite optimization guideline applies to the specific predicate only. If both statement-level and predicate-level query rewrite optimization guidelines are specified, the predicate-level guideline overrides the statement-level guideline for the specific predicate.
Each query rewrite optimization guideline is represented by a corresponding rewrite request element in the optimization guideline schema.
SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
FROM "Samp".PARTS P, "Samp".SUPPLIERS S, "Samp".PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY
AND S.S_SUPPKEY = PS.PS_SUPPKEY
AND P_SIZE IN (35, 36, 39, 40)
AND S.S_NATION IN ('INDIA', 'SPAIN')
AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST)
FROM "Samp".PARTSUPP PS1, "Samp".SUPPLIERS S1
WHERE P.P_PARTKEY = PS1.PS_PARTKEY
AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
AND S1.S_NATION = S.S_NATION)
ORDER BY S.S_NAME
<OPTGUIDELINES><INLIST2JOIN TABLE='P'/></OPTGUIDELINES>;
This
particular query rewrite optimization guideline specifies that the
list of constants in the predicate P_SIZE IN (35, 36, 39,
40) should be transformed into a table expression. This table
expression would then be eligible to drive an indexed nested-loop
join access to the PARTS table in the main subselect. The TABLE attribute
is used to identify the target IN-LIST predicate by indicating the
table reference to which this predicate applies. If there are multiple
IN-LIST predicates for the identified table reference, the INLIST2JOIN
rewrite request element is considered ambiguous and is ignored.SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
FROM "Samp".PARTS P, "Samp".SUPPLIERS S, "Samp".PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY
AND S.S_SUPPKEY = PS.PS_SUPPKEY
AND P_SIZE IN (35, 36, 39, 40)
AND P_TYPE IN ('BRASS', 'COPPER')
AND S.S_NATION IN ('INDIA', 'SPAIN')
AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST)
FROM "Samp".PARTSUPP PS1, "Samp".SUPPLIERS S1
WHERE P.P_PARTKEY = PS1.PS_PARTKEY
AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
AND S1.S_NATION = S.S_NATION)
ORDER BY S.S_NAME
<OPTGUIDELINES><INLIST2JOIN TABLE='P' COLUMN='P_SIZE'/></OPTGUIDELINES>;
The
TABLE attribute of the INLIST2JOIN element identifies the PARTS table
reference in the main subselect. The COLUMN attribute is used to identify
the IN-LIST predicate on the P_SIZE column as the target. In general,
the value of the COLUMN attribute can contain the unqualified name
of the column referenced in the target IN-LIST predicate. If the COLUMN
attribute is provided without the TABLE attribute, the query rewrite
optimization guideline is considered invalid and is ignored.<OPTGUIDELINES>
<INLIST2JOIN TABLE='P' COLUMN='P_SIZE' OPTION='DISABLE'/>
</OPTGUIDELINES>
<OPTGUIDELINES><INLIST2JOIN OPTION='DISABLE'/></OPTGUIDELINES>
SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
FROM "Samp".PARTS P, "Samp".SUPPLIERS S, "Samp".PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY
AND S.S_SUPPKEY = PS.PS_SUPPKEY
AND P_SIZE IN (35, 36, 39, 40)
AND P_TYPE = 'BRASS'
AND S.S_NATION IN ('INDIA', 'SPAIN')
AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST)
FROM "Samp".PARTSUPP PS1, "Samp".SUPPLIERS S1
WHERE P.P_PARTKEY = PS1.PS_PARTKEY
AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
AND S1.S_NATION = S.S_NATION)
ORDER BY S.S_NAME
<OPTGUIDELINES><SUBQ2JOIN OPTION='DISABLE'/></OPTGUIDELINES>;
SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
FROM "Samp".PARTS P, "Samp".SUPPLIERS S, "Samp".PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY
AND S.S_SUPPKEY = PS.PS_SUPPKEY
AND P_SIZE IN (35, 36, 39, 40)
AND P_TYPE = 'BRASS'
AND S.S_NATION IN ('INDIA', 'SPAIN')
AND NOT EXISTS (SELECT 1
FROM "Samp".SUPPLIERS S1
WHERE S1.S_SUPPKEY = PS.PS_SUPPKEY)
ORDER BY S.S_NAME
<OPTGUIDELINES><NOTEX2AJ OPTION='ENABLE'/></OPTGUIDELINES>;
SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
FROM "Samp".PARTS P, "Samp".SUPPLIERS S, "Samp".PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY
AND S.S_SUPPKEY = PS.PS_SUPPKEY
AND P_SIZE IN (35, 36, 39, 40)
AND P_TYPE = 'BRASS'
AND S.S_NATION IN ('INDIA', 'SPAIN')
AND PS.PS_SUPPKEY NOT IN (SELECT S1.S_SUPPKEY
FROM "Samp".SUPPLIERS S1
WHERE S1.S_NATION = 'CANADA')
ORDER BY S.S_NAME
<OPTGUIDELINES><NOTIN2AJ OPTION='ENABLE'/></OPTGUIDELINES>
A particular query rewrite optimization guideline might not be applicable when considered within the context of other query rewrite transformations being applied to the statement. That is, if a guideline request to enable a transform cannot be applied, a warning is returned. For example, an INLIST2JOIN rewrite enable request element targeting a predicate that is eliminated from the query by another query transformation would not be applicable. Moreover, the successful application of a query rewrite optimization guideline might change the applicability of other query rewrite transformation rules. For example, a request to transform an IN-LIST to a table expression might prevent a different IN-LIST from being transformed to a table expression, because the optimizer will only apply a single IN-LIST-to-join transformation per query block.