Embedded optimization guidelines

You can also specify optimization guidelines through embedded optimization guidelines, which require no extra configuration with SYSTOOLS.OPT_PROFILE tables. These are optimization guidelines within C-style comments placed at the end of SQL statements.

To use embedded optimization guidelines, simply surround the XML <OPTGUIDELINES> start tag and the </OPTGUIDELINES> end tag of your optimization guideline with a C-style comment. Place the comment at the end of your SQL statement. For example:
SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT 
FROM "Tpcd".PARTS, "Tpcd".SUPPLIERS S, "Tpcd".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 "Tpcd".PARTSUPP PS1, "Tpcd".SUPPLIERS S1 
   WHERE "Tpcd".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 ROWS 
/* <OPTGUIDELINES><IXSCAN TABLE=’"Tpcd".PARTS’/></OPTGUIDELINES> */
General rules when using embedded optimization guidelines:
  • Embedded optimization guidelines can only be applied to Data Manipulation Language (DML) statements: the SELECT, INSERT, UPDATE, DELETE, and MERGE commands. The optimizer will ignore such comments on other types of statements. No error or warning will be provided.
  • The embedded optimization guideline must be provided after the SQL portion of the statement. They cannot appear inside subselects. However, other types of comments can be provided at the end of the statement before or after the optimization guideline.
  • The optimizer will look for one embedded optimization guideline comment for every DML statement. If there are multiple embedded optimization guideline comments, all of them are ignored and a warning is produced.
  • The optimization guideline must be written in well-formed XML. It cannot include extraneous text.
  • Embedded optimization guidelines override identical optimization guidelines specified in the global section of an optimization profile.
  • Optimization guidelines provided by way of a statement profile section of an optimization profile take precedence over embedded optimization guidelines. That is, if the CURRENT OPTIMIZATION PROFILE register contains the name of an optimization profile, and the specified optimization profile contains a matching statement profile for a statement with embedded optimization guidelines, then the embedded optimization guidelines are ignored by the optimizer.