Neoview Control Query Default (CQD) Reference Guide (R2.5)

ROBUST_QUERY_OPTIMIZATION
Influencing Query PlansCategory
Provides a simpler way to influence the optimizer's choice of query plans. The optimizer
chooses query plans based on cardinality estimates (the number of result rows estimated
at each step of a query execution plan). Actual cardinalities encountered at query execution
often differ from estimates. The optimizer considers risky choices and exacts risk premiums
before it chooses a plan that is inherently sensitive to cardinality estimation errors. Risky
choices include:
• Nested joins: Can be excellent data reducers but they can also result in extremely
long-running queries when their outer table cardinality is grossly underestimated.
• Serial plans: Are an excellent choice because they use the least resources when processing
low data volumes. But they can also result in very long-running queries when actual
cardinalities greatly exceed estimates.
• Complete sharing of ESP partitioning schemes: Correct parallel processing requires
partitioning the data across ESP instances using a partitioning scheme usually based
on the largest table’s partitioning keys, join columns, and group by columns. Complete
sharing of ESP partitioning schemes minimizes the overhead of runtime repartitioning.
But, it can also result in very long-running queries if the "least common denominator"
partitioning scheme results in a few active ESPs doing most of the work. This can happen
when repartitioning is being done on a very low unique entry count attribute. For
example, gender.
ROBUST_QUERY_OPTIMIZATION can be used to influence the premiums associated
with these risky plans and thereby overall plan quality and performance for your specific
workloads.
Description
26 Optimizer