8.1.2 Push predicates inside UNION ALL legs and OUTER JOIN query blocks The example that is shown in Figure 8-1 demonstrates performance challenges when UNION  ALL is combined with an OUTER JOIN. The original query appears at the top of Figure 8-1  and is a simple two table (left outer) join of T1 to T2.  In this example, T1 and T2 are “archive enabled” (which refers to the Db2 11 archive  transparency feature). Therefore, Db2 rewrites the query to include the active and archive  tables (with T1 circled and the arrow pointing to the first UNION ALL on the left side of the  join, and T2 circled with arrow pointing to the second UNION ALL on the right side of the join).  This representation is true of any UNION ALL within a view, where the view definition is  replaced within the query where it is referenced. Although older releases provided similar  merge (and therefore materialization avoidance) capabilities for table functions that were  syntactically equivalent to views, Db2 12 improved the merge of deterministic table functions  with input parameters and indexability of input parameters as predicates within the table  function, as described in 8.2, “Predicate optimizations” on page 257. The performance challenge for this query example is that Db2 runs each leg of the UNION  ALL separately and combines the results from each side of the first UNION ALL. Then, it  combines each side of the second UNION ALL before joining the two results as requested in  the outer query block.  In the V11 rewrite of the query, no join predicates or any filtering within the UNION ALL legs  are used. The term  combining  means that Db2 returns all columns and all rows from T1 in the  first UNION ALL and all columns and all rows from H1 and materialize those rows to a work  file. The work file then is sorted in preparation for the join. This process is repeated for the  second UNION ALL; that is, all columns and all rows from T2 and H2 are materialized into a  work file and sorted in preparation for the join on column C1 from both work files. The performance of this query depends heavily on the size of the tables that are involved,  with large tables that use significant CPU and work file resources to complete the process. 246  IBM Db2 12 for z/OS Performance Topics Figure 8-1 Db2 11 left OUTER JOIN query with archive transparency tables