Friday, September 12, 2014

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! 

3 comments:

  1. Hello,

    I get the same behaviour you describe on my Oracle 11.2.0.3 DB. However I found the example below where the NO_PUSH_PRED hint works on that same DB.

    Preparation
    -----------

    create table t1(c1, c2)
    as select level, mod(level, 100)
    from dual
    connect by level <= 4000;

    create table t2(c1, c2, c3)
    as select mod(level, 4000), level, level
    from dual
    connect by level <= 100000;

    alter table t1 add constraint pk_t1 primary key (c1);

    alter table t2 add constraint pk_t2 primary key (c1, c2);

    exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

    exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false);

    Test Execution
    --------------
    select t1.c1, t1.c2, v.*
    from t1,
    (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
    from t2
    group by c1) v
    where
    t1.c1 = v.c1(+)
    and t1.c2 in (1, 2);

    SQL_ID f04x5crt5afz9, child number 0
    -------------------------------------

    Plan hash value: 2725946496

    ----------------------------------------------------------------------------------------
    | Id | Operation | Name | E-Rows | Cost (%CPU)| A-Rows |Buffers |
    ----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | 1092 (100)| 80 | 2094 |
    | 1 | NESTED LOOPS OUTER | | 80 | 1092 (1)| 80 | 2094 |
    |* 2 | TABLE ACCESS FULL | T1 | 80 | 7 (15)| 80 | 9 |
    | 3 | VIEW PUSHED PREDICATE | | 1 | 14 (0)| 80 | 2085 |
    | 4 | SORT GROUP BY | | 1 | 14 (0)| 80 | 2085 |
    | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 25 | 14 (0)| 2000 | 2085 |
    |* 6 | INDEX RANGE SCAN | PK_T2 | 25 | 1 (0)| 2000 | 85 |
    ----------------------------------------------------------------------------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(("T1"."C2"=1 OR "T1"."C2"=2))
    6 - access("C1"="T1"."C1")


    select /*+ no_push_pred(v) */
    t1.c1, t1.c2, v.*
    from t1,
    (select /*+ no_merge index(t2) */ c1, sum(c2), max(c3)
    from t2
    group by c1) v
    where
    t1.c1 = v.c1(+)
    and t1.c2 in (1, 2);

    SQL_ID 2cbu0t9tvgfmy, child number 0
    -------------------------------------

    Plan hash value: 1823425757

    ----------------------------------------------------------------------------------------
    | Id | Operation | Name | E-Rows | Cost (%CPU)| A-Rows |Buffers |
    ----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | 50386 (100)| 80 | 100K|
    |* 1 | HASH JOIN OUTER | | 80 | 50386 (1)| 80 | 100K|
    |* 2 | TABLE ACCESS FULL | T1 | 80 | 7 (15)| 80 | 9 |
    | 3 | VIEW | | 4000 | 50379 (1)| 4000 | 100K|
    | 4 | HASH GROUP BY | | 4000 | 50379 (1)| 4000 | 100K|
    | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 50379 (1)| 100K| 100K|
    | 6 | INDEX FULL SCAN | PK_T2 | 100K| 139 (6)| 100K| 264 |
    ----------------------------------------------------------------------------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("T1"."C1"="V"."C1")
    2 - filter(("T1"."C2"=1 OR "T1"."C2"=2))

    ReplyDelete
  2. Hello,

    Just an add-on to my previous comment (which I was not able to post within the same comment due to space limitation).

    Looking at the execution plans in your example, I think the optimizer believes it is not doing predicate pushdown in any of the cases. This would explain why the NO_PUSH_PRED hint has no effect. I see the following indications supporting my claim:
    - The "VIEW PUSHED PREDICATE" does appear in none of the plans.
    - In my example the NO_QUERY_TRANSFORMATION hint disables predicate pushdown (even without a NO_PUSH_PRED hint), whereas in your example the restriction "T2"."USERNAME"='OP' is evaluated within the view T_VIEW1 despite the NO_QUERY_TRANSFORMATION hint.

    The Oracle documentation on the PUSH_PRED / NO_PUSH_PRED hints states they control join predicate push down (http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm). As the predicate "T2"."USERNAME"='OP' in your example is not used for a join, it is maybe not considered a join predicate and thus not affected by NO_PUSH_PRED hint?
    Maybe the 10053 trace might clarify if the optimizer does or does not believe it is doing a predicate push-down.

    I do however not understand, what the operation/transformation executed by the optimizer in your example would be (if not a predicate push-down) and why it seems not to be affected by the NO_QUERY_TRANSFORMATION hint.

    regards
    Martin

    ReplyDelete
  3. Hi Martin! Well it's a mystery that's for sure. What really is a pushed predicate? So it is only thru some sort of join? That seems odd but maybe that is the case, so what is it called in my example? I'd think that would be a pushed predicate as well. And on top of that the example from the docs doesn't work as well. You'd think they'd have an example that really did demonstrate the functionality.

    As is somewhat typical of hints, we really don't know how they work.

    It's interesting that both the no_merge and the index hint are needed to get this example to show the pushing and non-pushing of the predicate. It will not push the predicate into the view when using a full table scan view created on T2.

    I've rerun all the examples here (my two simple ones and you two more complex ones) and done a 10053 trace on them. Looks that is it very much about the join. For my two example there is a reference like this:

    Predicate Move-Around (PM)
    **************************
    PM: Passed validity checks.
    PM: Pushed down predicate "T2"."USERNAME"='OP'
    from query block SEL$1 (#0) to query block SEL$2 (#0)


    For your examples there is this:


    JPPD: Considering Cost-based predicate pushdown from query block SEL$1 (#1)
    ************************************
    Cost-based predicate pushdown (JPPD)
    ************************************
    kkqctdrvTD-start on query block SEL$1 (#1)


    For the first example it actually does the push down and that appears to be happening in/around these lines in the trace (which follow the above a few lines down):

    Cost-Based Join Predicate Push-down
    ***********************************
    JPPD: Checking validity of push-down in query block SEL$1 (#1)
    JPPD: Checking validity of push-down from query block SEL$1 (#1) to query block SEL$2 (#2)
    Check Basic Validity for Non-Union View for query block SEL$2 (#2)
    JPPD: Passed validity checks
    JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.
    Join-Predicate push-down on query block SEL$1 (#1)

    The second one where it doesn't do the push down has a line like this:

    Check Basic Validity for Non-Union View for query block SEL$2 (#2)
    JPPD: JPPD bypassed: hinted.

    So we can see that the hint stopped if from doing the push down.

    Cool stuff.

    Thanks much for the example!!

    ReplyDelete