The cost-based optimizer tries to merge views whenever possible but sometimes we ma y want to override this behavior; that is, we may want the optimizer to push predicates into the view instead of merging the view into the main query. For example, consider the following query. We use an inline view for demonstration purposes but we could equally well have used a predefined 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;

We used the NO_MERGE hint to prevent the cost-based optimizer from following its natural inclination to merge the view. We also used the PUSH_PRED hint to ensure that the hint is pushed into the view. Let’s see what happens:

Plan hash value: 155493165

------------------------------------------------------
| Id  | Operation                    | Name          |
------------------------------------------------------
|   0 | SELECT STATEMENT             |               |
|   1 |  NESTED LOOPS                |               |
|   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"))

The optimizer obeyed the NO_MERGE hint but ignored the PUSH_PRED hint. A full table scan was used instead of an index. This blog post by Maria Colgan explains that join-predicate pushdown cannot be used for inner-joined views because the optimizer development team decided that inner-joined views must be merged.

The strange solution is then to convert the inner join to an outer join. Fortunately for us, the conversion will not change the results because EMAIL is a unique non-null column (there is a unique index on it).

SQL> DESCRIBE employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

select index_name, uniqueness
from user_indexes
where table_name='EMPLOYEES'
order by index_name;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_EMAIL_UK                   UNIQUE
EMP_EMP_ID_PK                  UNIQUE
EMP_NAME_IX                    NONUNIQUE

select column_name
from user_ind_columns
where index_name='EMP_EMAIL_UK';

COLUMN_NAME
------------------------------
EMAIL

Converting the inner join to an outer join achieves the desired results.

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: 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)
   5 - filter("JOB_ID"="E"."JOB_ID")
   6 - access("EMAIL"="E"."EMAIL")

P.S. The good news is that these shenanigans are not needed beginning with Oracle Database 12.1.0.2 which supports join-predicate pushdown  for inner-joined views that are non-mergeable by virtue of the NO_MERGE hint.