However, the referencing SELECT requires P2.P_PARTKEY only (for the SELECT list and  ON clause). Because of the materialization of the UNION ALL table expression in Db2 11, all  columns from the PART table (for example, if the table has 10 columns) are accessed and  materialized to the work file. As the outer query is retrieving from that work file, only  P_PARTKEY is retrieved. The example that is shown in Figure 8-5 on page 250 is repeated in Figure 8-6. Db2 12  prunes the unnecessary columns and requires only P_PARTKEY to be returned from each  UNION ALL leg, whereas Db2 11 returned all columns. If the UNION ALL table expression is  materialized, only P_PARTKEY is retrieved and written or materialized to work file compared  with all (10) columns in Db2 11.  If P_PARTKEY is indexed, the optimizer can choose a non-matching index scan in Db2 12  rather than a table space scan in Db2 11. Similarly, if the join predicates were pushed down in  Db2 12 and matching index access was chosen on P_PARTKEY, index-only is possible  because only P_PARTKEY is required. Another benefit of column pruning is that it can open other table pruning opportunities. In Db2  10, an enhancement was delivered to prune OUTER JOIN tables if no columns were required  from that table and it was guaranteed that the table did not introduce duplicates. In Db2 12,  this pruning enhancement is extended from OUTER JOINs to views or table expressions that  are guaranteed not to return duplicates and no columns are required from the view or table  expression. The example that is shown in Figure 8-7 on page 252 might appear convoluted, but it is a  simplified example from an actual ERP workload on Db2 for z/OS. Before explaining the  example, it is an interesting question to ask why you might build a query that references  tables from which columns are not actually required.  This type of construct is becoming more common as views are built to hide the complexity  and present a simplified representation to the user, such that they can select from a view,  rather than writing a complex query themselves. Similarly, for query generators it is required  that the select from the views and the underlying table relationships can be set up or altered  without affecting the query generation.  Chapter 8. Query performance  251 Table pruning Figure 8-6 Db2 12 pruning unreferenced columns with optional join pushdown