Query performance enhancements

Db2 12 introduces performance enhancements for queries that use any of the following: outer joins, UNION ALL, archive transparency, system-period temporal tables.

Db2 12 introduces the following enhancements to query performance:
  • Trimming columns from a materialized view or table expression if those columns are not required by the outer query
  • Pruning unique LEFT OUTER JOIN views or table expressions if columns are not required in SELECT list
  • Pushing join predicates into UNION ALL or outer join query blocks if it is cost effective for the optimizer to do so
  • Push ORDER BY and FETCH FIRST into UNION ALL legs to allow each UNION ALL leg to avoid a sort (if possible) and fetch only the required rows for the outer query (for FETCH FIRST)
  • Reorder outer join tables to avoid unnecessary materializations
  • Reduce situations where workfile usage is required for materialized outer join query blocks or UNION ALL legs