title: Pushing predicate in a view
body: <p>The scenerio </p>
  
  <pre><code>explain plan for 
    select l.etl_id , v.*
    from   v_load_base v, etl_log l
    where  l.is_active = 1
    and    v.ddate between trunc(l.load_from_date) and l.load_to_date
    and    v.starttime_full between l.load_from_date and l.load_to_date;
  </code></pre>
  
  <p>Produces this execution plan </p>
  
  <pre><code>--------------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |               |     3 |   444 |       | 31624   (4)| 00:06:20 |
  |   1 |  SORT ORDER BY                 |               |     3 |   444 |       | 31624   (4)| 00:06:20 |
  |*  2 |   HASH JOIN                    |               |     3 |   444 |       | 31623   (4)| 00:06:20 |
  |   3 |    NESTED LOOPS OUTER          |               |     3 |   378 |       | 31413   (4)| 00:06:17 |
  |*  4 |     HASH JOIN                  |               |     3 |   348 |       | 31410   (4)| 00:06:17 |
  |*  5 |      HASH JOIN                 |               |  1252 |   118K|  2144K| 23428   (4)| 00:04:42 |
  |*  6 |       HASH JOIN                |               | 27786 |  1818K|       |   764   (7)| 00:00:10 |
  |   7 |        NESTED LOOPS            |               |     8 |   264 |       |     7   (0)| 00:00:01 |
  |*  8 |         TABLE ACCESS FULL      | ETL_LOG       |     1 |    21 |       |     3   (0)| 00:00:01 |
  |*  9 |         TABLE ACCESS FULL      | MD            |     8 |    96 |       |     4   (0)| 00:00:01 |
  |  10 |        TABLE ACCESS FULL       | DS            |   479K|    15M|       |   748   (6)| 00:00:09 |
  |  11 |       TABLE ACCESS FULL        | MDS           |  7280K|   208M|       |  7823   (5)| 00:01:34 |
  |  12 |      TABLE ACCESS FULL         | TASKS         |  7760K|   140M|       |  7844   (5)| 00:01:35 |
  |  13 |     TABLE ACCESS BY INDEX ROWID| ETL_GIS       |     1 |    10 |       |     1   (0)| 00:00:01 |
  |* 14 |      INDEX UNIQUE SCAN         | ETL_GIS_UK    |     1 |       |       |     0   (0)| 00:00:01 |
  |  15 |    TABLE ACCESS FULL           | DETAILS_TABLE |   292K|  6280K|       |   204   (8)| 00:00:03 |
  --------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>The join predicate with the table <code>etl_log</code> was pushed down to the view <code>v_load_base</code> (line 8).</p>
  
  <p>I created a view called <code>v_load_base_active</code> based on the same exact query as the one above.
  Querying the new view produces the following plan </p>
  
  <pre><code>explain plan for select * from v_load_base_active;
  
  ----------------------------------------------------------------------------------------------------
  | Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT          |                |     3 |   861 |       | 63583   (8)| 00:12:43 |
  |   1 |  NESTED LOOPS             |                |     3 |   861 |       | 63583   (8)| 00:12:43 |
  |*  2 |   TABLE ACCESS FULL       | ETL_LOG        |     1 |    21 |       |     3   (0)| 00:00:01 |
  |*  3 |   VIEW                    | V_LOAD_BASE    |     3 |   798 |       | 63580   (8)| 00:12:43 |
  |   4 |    SORT ORDER BY          |                |   422K|    51M|   110M| 63580   (8)| 00:12:43 |
  |*  5 |     HASH JOIN RIGHT OUTER |                |   422K|    51M|       | 51513   (9)| 00:10:19 |
  |   6 |      TABLE ACCESS FULL    | ETL_GIS        |  5958 | 59580 |       |    17   (0)| 00:00:01 |
  |*  7 |      HASH JOIN            |                |   422K|    47M|  9712K| 51488   (9)| 00:10:18 |
  |   8 |       TABLE ACCESS FULL   | LINES_DETAILS  |   292K|  6280K|       |   204   (8)| 00:00:03 |
  |*  9 |       HASH JOIN           |                |   422K|    38M|    35M| 48647  (10)| 00:09:44 |
  |* 10 |        HASH JOIN          |                |   422K|    30M|       | 27365  (14)| 00:05:29 |
  |  11 |         TABLE ACCESS FULL | MD             |  3103 | 37236 |       |     4   (0)| 00:00:01 |
  |* 12 |         HASH JOIN         |                |  7301K|   445M|    21M| 24366   (3)| 00:04:53 |
  |  13 |          TABLE ACCESS FULL| DS             |   479K|    15M|       |   748   (6)| 00:00:09 |
  |  14 |          TABLE ACCESS FULL| MSD            |  7280K|   208M|       |  7823   (5)| 00:01:34 |
  |  15 |        TABLE ACCESS FULL  | TASKS          |  7760K|   140M|       |  7844   (5)| 00:01:35 |
  ----------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>The predicate is not pushed. This leads to a major decrease in performance. </p>
  
  <p>I've tried setting a hint explicitly in the view <code>/*+ PUSH_PRED(v) */</code> but the plan does not change.</p>
  
  <p>How can i make the optimizer push the predicate also within a view ... ? </p>
  
  <ul>
  <li><code>v_load_base</code> does not contain analytic functions. The first query proves that the predicate <strong>can</strong> be pushed.</li>
  </ul>
  
  <p><strong>EDIT</strong></p>
  
  <p>notice that oracle does not state in the execution plan that a predicate was pushed with <code>VIEW PUSHED PREDICATE</code>. but, looking at the plan it's clear that oracle transformed the view's sql to include the <code>etl_log</code> predicate.</p>