title: Oracle Optimizer issue with UNION ALL
body: <p>I'm pretty confused by this behavior from Oracle optimizer. This has to do with union all operations from a CTE. If anyone has any ideas I'm all game. </p>
  
  <pre><code> --Relevant data structures:
   --t_positionperf (index: POSITIONPERF_X1 (account_id, hist_date, security_id)
   --t_positionhist (index: POSITIONHIST_X1 (position_id, hist_date, hist_type)
   --                       PK_T_POSITIONHIST (hist_date, position_id, hist_type)
   --v_positiontype (very simple "case-when" translation of a tiny lookup table)
  
  with q as (
  select 
  pp.position_id, pp.hist_date, pp.account_id, pp.income, pp.expense,
  ph.position_type_id, ph.price, ph.quantity, ph.factor, ph.daily_accrual,
  n.daily_accrual as new_daily_accrual, nvl(n.is_loan, v.is_loan) as new_is_loan
  from
  t_positionperf pp
  left outer join t_positionhist ph
              on  pp.position_id = ph.position_id
              and pp.hist_date = ph.hist_date
              and ph.hist_type = 'O' --the 'O' join set from t_positionhist
       left outer join v_positiontype v 
                   on  ph.position_type_id = v.position_type_id
       left outer join (
                        select 
                        x.position_id, x.hist_date, x.daily_accrual, v2.is_loan 
                        from t_positionhist x 
                        join v_positiontype v2 on x.position_type_id = v2.position_type_id
                        where x.hist_type = 'N'
                       ) n --the 'N' join set from t_positionhist
                  on  ph.position_id = n.position_id 
                  and ph.hist_date = n.hist_date
  where pp.account_id in (5018,5312)
  and pp.hist_date between to_date('01-jan-14') and  to_date('31-jan-14')
  )
  select 
  q.account_id,q.hist_date,q.position_id,q.income,
  q.expense,q.position_type_id,q.price,q.quantity,q.factor
  from q
  </code></pre>
  
  <p>This uses a sensible access path. </p>
  
  <pre><code>Statistics
  ----------------------------------------------------------
           29  recursive calls
            0  db block gets
        37640  consistent gets
         5115  physical reads
            0  redo size
       227442  bytes sent via SQL*Net to client
         4952  bytes received via SQL*Net from client
          409  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
         6116  rows processed
  
  
  Execution Plan:
  --------------------------------------------------------------------------------------------------------
  | Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)  | Time     |
  --------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                 |                   |   941 | 88454 |    5283   (1)| 00:01:04 |
  |*  1 |  FILTER                          |                   |       |       |              |          |
  |   2 |   NESTED LOOPS OUTER             |                   |   941 | 88454 |    5283   (1)| 00:01:04 |
  |*  3 |    HASH JOIN RIGHT OUTER         |                   |   941 | 71516 |    2456   (1)| 00:00:30 |
  |   4 |     TABLE ACCESS FULL            | T_POSITIONTYPE    |    64 |  1216 |       3   (0)| 00:00:01 |
  |   5 |     NESTED LOOPS OUTER           |                   |   941 | 53637 |    2452   (1)| 00:00:30 |
  |   6 |      INLIST ITERATOR             |                   |       |       |              |          |
  |   7 |       TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF    |   941 | 22584 |     567   (1)| 00:00:07 |
  |*  8 |        INDEX RANGE SCAN          | POSITIONPERF_X1   |   941 |       |       6   (0)| 00:00:01 |
  |   9 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST    |     1 |    33 |       2   (0)| 00:00:01 |
  |* 10 |       INDEX UNIQUE SCAN          | POSITIONHIST_X1   |     1 |       |       1   (0)| 00:00:01 |
  |  11 |    VIEW PUSHED PREDICATE         |                   |     1 |    18 |       3   (0)| 00:00:01 |
  |  12 |     NESTED LOOPS                 |                   |     1 |    27 |       4   (0)| 00:00:01 |
  |  13 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST    |     1 |    20 |       3   (0)| 00:00:01 |
  |* 14 |       INDEX UNIQUE SCAN          | POSITIONHIST_X1   |     1 |       |       2   (0)| 00:00:01 |
  |  15 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONTYPE    |     1 |     7 |       1   (0)| 00:00:01 |
  |* 16 |       INDEX UNIQUE SCAN          | PK_T_POSITIONTYPE |     1 |       |       0   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>However, if you add a UNION ALL to this select from the CTE, so that that the query looks like:</p>
  
  <pre><code>with q as (...) 
  select q.account_id, q... etc.
  UNION ALL
  select q.account_id, max(q...) etc.
  group by q.account_id
  </code></pre>
  
  <p>The Optimizer now wants to materialize the CTE (TEMP TABLE TRANSFORMATION). This part makes sense to me because of the UNION ALL from the same CTE. What I <em>don't</em> understand is why materializing the CTE causes it to choose a different (and much inferior) access path. It seems to lose the ability to push the predicate to the T_POSITIONHIST table when building the 'N' join set. You can see that it now does a Full Table Scan on T_POSITIONHIST, and as a result the disk I/O has gone <strong>through the roof</strong>:</p>
  
  <pre><code>Statistics
  ----------------------------------------------------------
          362  recursive calls
           53  db block gets
       546116  consistent gets
       521527  physical reads
          688  redo size
       250541  bytes sent via SQL*Net to client
         4952  bytes received via SQL*Net from client
          409  SQL*Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
         6117  rows processed
  
  
  Execution Plan
  ------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |                             |  1882 |   205K|    13  (54)| 00:00:01 |
  |   1 |  TEMP TABLE TRANSFORMATION         |                             |       |       |            |          |
  |   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6CB3_54C7EBF8 |       |       |            |          |
  |*  3 |    FILTER                          |                             |       |       |            |          |
  |*  4 |     HASH JOIN OUTER                |                             |   941 | 81867 |   147K  (3)| 00:29:32 |
  |*  5 |      HASH JOIN RIGHT OUTER         |                             |   941 | 63047 |  2456   (1)| 00:00:30 |
  |   6 |       VIEW                         | V_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
  |   7 |        TABLE ACCESS FULL           | T_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
  |   8 |       NESTED LOOPS OUTER           |                             |   941 | 56460 |  2452   (1)| 00:00:30 |
  |   9 |        INLIST ITERATOR             |                             |       |       |            |          |
  |  10 |         TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF              |   941 | 22584 |   567   (1)| 00:00:07 |
  |* 11 |          INDEX RANGE SCAN          | POSITIONPERF_X1             |   941 |       |     6   (0)| 00:00:01 |
  |  12 |        TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST              |     1 |    36 |     2   (0)| 00:00:01 |
  |* 13 |         INDEX UNIQUE SCAN          | POSITIONHIST_X1             |     1 |       |     1   (0)| 00:00:01 |
  |  14 |      VIEW                          |                             |  2358K|    44M|   145K  (3)| 00:29:02 |
  |  15 |       VIEW                         |                             |  2358K|    51M|   145K  (3)| 00:29:02 |
  |* 16 |        HASH JOIN                   |                             |  2358K|    67M|   145K  (3)| 00:29:02 |
  |  17 |         VIEW                       | V_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
  |  18 |          TABLE ACCESS FULL         | T_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
  |* 19 |         TABLE ACCESS FULL          | T_POSITIONHIST              |  2358K|    51M|   145K  (3)| 00:29:01 |
  |  20 |   UNION-ALL                        |                             |       |       |            |          |
  |  21 |    VIEW                            |                             |   941 |   106K|     6   (0)| 00:00:01 |
  |  22 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6CB3_54C7EBF8 |   941 | 56460 |     6   (0)| 00:00:01 |
  |  23 |    HASH GROUP BY                   |                             |   941 |    99K|     7  (15)| 00:00:01 |
  |  24 |     VIEW                           |                             |   941 |    99K|     6   (0)| 00:00:01 |
  |  25 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6CB3_54C7EBF8 |   941 | 56460 |     6   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>The weird part is, you can partially get around this issue by <em>duplicating</em> the CTE, like so:</p>
  
  <pre><code>with q as (...), q2 as (.../*duplicate of q*/)
  select q.account_id, q... etc.
  UNION ALL
  select q2account_id, max(q2...) etc.
  group by q2.account_id
  </code></pre>
  
  <p>Because it no longer wants to materialize the result set, it's able to use the smarter access path, except it has to do it twice, so the cost is approximately doubled (but still far less than the really bad access path it uses above). </p>
  
  <h2>Statistics</h2>
  
  <pre><code>     57  recursive calls
        0  db block gets
    73855  consistent gets
        0  physical reads
        0  redo size
   202937  bytes sent via SQL*Net to client
     4953  bytes received via SQL*Net from client
      409  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
     6117  rows processed
  </code></pre>