The PUSH_PRED hint instructs the optimizer to push a join predicate into a view. Here is an example. The cost-based optimizer chooses to push the second predicate “e.job_id = v.job_id” and use the EMP_JOB_IX index. Note that we used the NO_MERGE hint in conjunction with the PUSH_PRED hint to prevent the cost-based optimizer from following its natural inclination to merge the view.

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
    FROM employees e,
         (SELECT email, job_id
            FROM employees
         ) v
  WHERE e.email = v.email(+)
    AND e.job_id = v.job_id(+)
    AND e.employee_id = 100;

Plan hash value: 860241558

-------------------------------------------------------
| Id  | Operation                     | Name          |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |               |
|   1 |  NESTED LOOPS OUTER           |               |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |
|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |
|   4 |   VIEW PUSHED PREDICATE       |               |
|*  5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|*  6 |     INDEX RANGE SCAN          | EMP_JOB_IX    |
-------------------------------------------------------

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

   3 - access("E"."EMPLOYEE_ID"=100)
   5 - filter("EMAIL"="E"."EMAIL")
   6 - access("JOB_ID"="E"."JOB_ID")

Conversely, the NO_PUSH_PRED hint instructs the optimizer not to push any join predicate into a view. Here is an example.

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
    FROM employees e,
         (SELECT email, job_id
            FROM employees
         ) v
  WHERE e.email = v.email(+)
    AND e.job_id = v.job_id(+)
    AND e.employee_id = 100;

Plan hash value: 1940769814

------------------------------------------------------
| Id  | Operation                    | Name          |
------------------------------------------------------
|   0 | SELECT STATEMENT             |               |
|   1 |  NESTED LOOPS OUTER          |               |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |
|*  4 |   VIEW                       |               |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES     |
------------------------------------------------------

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

   3 - access("E"."EMPLOYEE_ID"=100)
   4 - filter(("E"."EMAIL"="V"."EMAIL" AND "E"."JOB_ID"="V"."JOB_ID"))

Note that are actually two choices of predicates—“e.email = v.email” and “e.job_id = v.job_id”—but there isn’t a documented method of controlling which specific predicate to push.; it would appear that the decision is left to the cost-based optimizer. However, an undocumented method can be used. Both the PUSH_PRED and NO_PUSH_PRED hints accept an optional second parameter—the predicate number. Predicates are numbered in the order in which they appear in the SQL statement. In the following example, we use these undocumented variations to push the predicate “e.email = v.email” instead of “e.job_id = v.job_id” which the cost-based optimizer had chosen when left to its own devices.

SELECT /*+ NO_MERGE(v) PUSH_PRED(v 1) NO_PUSH_PRED(v 2) */ *
    FROM employees e,
         (SELECT email, job_id
            FROM employees
         ) v
  WHERE e.email = v.email(+)
    AND e.job_id = v.job_id(+)
    AND e.employee_id = 100;

Plan hash value: 2781406578

-------------------------------------------------------
| Id  | Operation                     | Name          |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |               |
|   1 |  NESTED LOOPS OUTER           |               |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |
|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |
|*  4 |   VIEW PUSHED PREDICATE       |               |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|*  6 |     INDEX UNIQUE SCAN         | EMP_EMAIL_UK  |
-------------------------------------------------------

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

   3 - access("E"."EMPLOYEE_ID"=100)
   4 - filter("E"."JOB_ID"="V"."JOB_ID")
   6 - access("EMAIL"="E"."EMAIL")

Happy hinting.