IC74702: SELECT COUNT(*) FROM VIEW UNION ALL OF TWO TABLES IS MUCH SLOWER THAN THE SAME QUERY WITHOUT THE VIEW

APAR status

• Closed as program error.

Error description

• You create a view that is a UNION ALL of two tables. You execute
  a SELECT COUNT(*) on
  the view. You compare the execution time with a SELECT COUNT(*)
  on a UNION ALL of the
  same tables and it executes much faster. The query plans show
  that the view query uses
  sequential scans and creates a temporary table. The select on
  the table unions uses index
  access and does not create a temporary table.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All 11.50 users.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A simple query containing aggregation on a simple UNION ALL  *
  * view (without group by, order by, distinct, aggregation,     *
  * etc) experiences performance issues due to materialization   *
  * of UNION ALL view before performing the aggregation.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC9.                                        *
  ****************************************************************
  

Problem conclusion

• The issue is fixed in 11.50.xC9, in case the main query only
  contains a single table (which is the UNION ALL view), and the
  main query is otherwise simple (no group by, order by,
  distinct). In this case the aggregation can be evaluated within
  each arm of the UNION ALL view, and then the partial aggregates
  can be aggregated together. This functionality requires the
  onconfig variable IFX_FOLDVIEW to be on.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP