Influence the Query Plans

When to Influence the Optimizer

• Poor Plan selected by the optimizer results in poor execution time
• Provide a workaround when query results in a compile time error
• Compilation takes a long time: Limit the search space to get better compile time
• In embedded static SQL applications values of host variables used in predicates are not known
at compile time, example
− WHERE T.col1 = :hv
− WHERE T.col1 BETWEEN :hv1 AND :hv2

Influencing the Optimizer —

Query Transformation …
• Query formulations sometimes can be tuned to help optimizer
    −Example: prefer use of joins instead of subqueries;
      while SQL/MX converts many subqueries into joins there are some correlated ones that it cannot
• Specify all applicable predicates for positioning and joins
• When using multi-valued predicates (row constructors) with clustering key columns force
MDAM to avoid scans.

Best Practices
• Give optimizer as much information as possible
      − Efficient predicates
      − Consider window predicates
• Use CQDs for optimizer hints
       − HIST_DEFAULT_SEL_FOR_PRED_RANGE
       − INTERACTIVE_ACCESS
• Some CQDs may be set globally in SYSTEM_DEFAULTS table
• Use CQS as last resort
− Only force basic outline of a plan and let optimizer find the optimal solution within the boundaries imposed by the command