NO_PUSH_PRED – the non-hint

I doubt this will be a big deal to most folks, just an interesting thing to know.   It certainly appears that the “NO_PUSH_PRED” hint does nothing.  I’ve tried every way I can think of to get this hint to work and no luck.  This test was done on a 12.1.0.1 database but I’ve done this on 11 (and I think 10 also) and can’t get this hint to do anything.  Of course I don’t know when or why someone wouldn’t want a predicate pushed, I can’t think of a good case that it shouldn’t be if it can be.  Sure there are lots of reasons why it shouldn’t be, the overall reason is that it would change the results of the view, which would be very bad.  The optimizer wouldn’t do that anyway so I don’t think we have to worry about that.

I have a simple example:

SQL> create table t1 as

  2  select * from all_users ;

Table created.

SQL> create table t2 as

  2  select * from all_users ;

Table created.

SQL> create or replace view t_view1 as

  2  select t1.user_id, t2.username

  3    from t1, t2

  4   where t1.user_id = t2.user_id;

View created.

Now selecting from the view t_view1 as such:

select /*+ NO_QUERY_TRANSFORMATION */ * from t_view1 v where username = 'OP'

I get this plan, I’m using the NO_QUERY_TRANSFORMATION  hint to keep it from doing view merging which it would do without the hint.  It’s easier to see the pushing for the predicate this way.  You can see that the predicate got pushed into the view resolution. 

Plan hash value: 1448425336

-------------------------------------------------------------------------------

| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |     1 |    79 |     5  (20)| 00:00:01 |

|   1 |  VIEW               | T_VIEW1 |     1 |    79 |     5  (20)| 00:00:01 |

|*  2 |   HASH JOIN         |         |     1 |    17 |     5  (20)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| T2      |     1 |    13 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| T1      |    45 |   180 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T1"."USER_ID"="T2"."USER_ID")

   3 - filter("T2"."USERNAME"='OP')

SQL>

Now I’ll change the query to this:

select /*+ NO_QUERY_TRANSFORMATION NO_PUSH_PRED(v)*/ * from t_view1 v where username = 'OP'

and I get this plan (which is the same one):

Plan hash value: 1448425336

-------------------------------------------------------------------------------

| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |     1 |    79 |     5  (20)| 00:00:01 |

|   1 |  VIEW               | T_VIEW1 |     1 |    79 |     5  (20)| 00:00:01 |

|*  2 |   HASH JOIN         |         |     1 |    17 |     5  (20)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| T2      |     1 |    13 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| T1      |    45 |   180 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T1"."USER_ID"="T2"."USER_ID")

   3 - filter("T2"."USERNAME"='OP')

I even looked at the 10053 trace to see if there was a clue there, nothing.  The hint is there in the plan but nothing like “hint ignored” like I saw with the USE_CONCATE hint.   I tried the table name, I tried using query block names, I tried putting the hint in the view, I tried the hint without a table alias or name, all end up with the same; the predicate gets pushed.

And just for kicks I looked up the example for this hint in the docs (Oracle® Database SQL Language Reference) and this is the query:

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *

  FROM employees e,

       (SELECT manager_id

          FROM employees) v

  WHERE e.manager_id = v.manager_id(+)

    AND e.employee_id = 100;

And guess what?  The plan is the same for that query with or without the hint!  It doesn’t push the predicate for either query. And I’m pretty sure that is because of the outer join between the main query and the inline view. 

The only way I can get it to not push the predicate is to define the view with rownum in it like this, notice in the plan this time the predicate is NOT pushed into the view, and is evaluated after the view is resolved:

create or replace view t_view2 as

  select rownum rno, t1.user_id, t2.username

    from t1, t2

    where t1.user_id = t2.user_id;

select /*+ NO_QUERY_TRANSFORMATION */ * from t_view2 v where username = 'OP'

Plan hash value: 3187783690

--------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |         |    45 |  4140 |     5  (20)| 00:00:01 |

|*  1 |  VIEW                | T_VIEW2 |    45 |  4140 |     5  (20)| 00:00:01 |

|   2 |   COUNT              |         |       |       |            |          |

|*  3 |    HASH JOIN         |         |    45 |   765 |     5  (20)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| T1      |    45 |   180 |     2   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| T2      |    45 |   585 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("USERNAME"='OP')

   3 - access("T1"."USER_ID"="T2"."USER_ID")

So if anyone can show me a repeatable example of using the NO_PUSH_PRED hint and it working, I’d love to see it!