create table tmp_acc_dict
(
acc_id number(10) not null,
acc_name varchar2(255)
);

create table tmp_acc_xm
(
acc_id number(10) not null,
sd date not null,
ed date,
some_value varchar2(255)
);

create table tmp_acc_filter
(
acc_id number(10) not null,
filter_date date not null
);

insert all 
into tmp_acc_dict
values
(1,'acc1')
into tmp_acc_dict
values
(2,'acc2')
into tmp_acc_xm
values (1,sysdate-30, sysdate-2,'abs')
into tmp_acc_xm
values (1,sysdate, sysdate+10,'abs2')
into tmp_acc_xm
values (1,sysdate+11, null,'abs3')
into tmp_acc_xm
values (2,sysdate-30, null,'abs4')
into tmp_acc_filter
values (1,sysdate-10)
into tmp_acc_filter
values (1,trunc(sysdate,'dd')+5/24)
into tmp_acc_filter
values (2,sysdate-5)
select 1 from dual;

create unique index tmp_acc_dict_pk on tmp_acc_dict (acc_id);
create index tmp_acc_xm_fk on tmp_acc_xm (acc_id);
create index tmp_acc_xm_irs on tmp_acc_filter (filter_date);


create or replace view tmp_test_view as
select 
--+ push_pred(b) 
a.acc_id, a.acc_name, b.acc_xm_res from tmp_acc_dict a
             left join
                      (
                      select 
                      acc_id,  max(sd) acc_xm_res
                      from tmp_acc_xm xm
                      where sd>trunc(sysdate,'dd')
                      group by acc_id
                      ) b on a.acc_id=b.acc_id;

select * from tmp_test_view a where acc_id in 
( select  acc_id from tmp_acc_filter b where filter_date>to_date(:1,'dd.mm.yyyy'))

----------------------------------------------------------------------------------------------
| Id   | Operation                        | Name            | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                 |                 |    2 |   350 |    9 | 00:00:01 |
|    1 |   NESTED LOOPS OUTER             |                 |    2 |   350 |    9 | 00:00:01 |
|    2 |    MERGE JOIN SEMI               |                 |    2 |   328 |    5 | 00:00:01 |
|    3 |     TABLE ACCESS BY INDEX ROWID  | TMP_ACC_DICT    |    2 |   284 |    2 | 00:00:01 |
|    4 |      INDEX FULL SCAN             | TMP_ACC_DICT_PK |    2 |       |    1 | 00:00:01 |
|  * 5 |     SORT UNIQUE                  |                 |    3 |    66 |    3 | 00:00:01 |
|    6 |      TABLE ACCESS BY INDEX ROWID | TMP_ACC_FILTER  |    3 |    66 |    2 | 00:00:01 |
|  * 7 |       INDEX RANGE SCAN           | TMP_ACC_XM_IRS  |    3 |       |    1 | 00:00:01 |
|    8 |    VIEW PUSHED PREDICATE         |                 |    1 |    11 |    2 | 00:00:01 |
|    9 |     SORT GROUP BY                |                 |    1 |    22 |    2 | 00:00:01 |
| * 10 |      TABLE ACCESS BY INDEX ROWID | TMP_ACC_XM      |    1 |    22 |    2 | 00:00:01 |
| * 11 |       INDEX RANGE SCAN           | TMP_ACC_XM_FK   |    1 |       |    1 | 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("A"."ACC_ID"="ACC_ID")
* 5 - filter("A"."ACC_ID"="ACC_ID")
* 7 - access("FILTER_DATE">TO_DATE(:1,'dd.mm.yyyy'))
* 10 - filter("SD">TRUNC(SYSDATE@!,'fmdd'))
* 11 - access("ACC_ID"="A"."ACC_ID")



select * from tmp_test_view a 
join tmp_acc_filter b on (a.acc_id=b.acc_id and b.filter_date>to_date(:1,'dd.mm.yyyy'))

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |    1 |   210 |    5 | 00:00:01 |
|   1 |   HASH GROUP BY                  |                 |    1 |   210 |    5 | 00:00:01 |
|   2 |    NESTED LOOPS OUTER            |                 |    1 |   210 |    4 | 00:00:01 |
|   3 |     NESTED LOOPS                 |                 |    1 |   188 |    3 | 00:00:01 |
|   4 |      TABLE ACCESS BY INDEX ROWID | TMP_ACC_FILTER  |    1 |    34 |    2 | 00:00:01 |
| * 5 |       INDEX RANGE SCAN           | TMP_ACC_XM_IRS  |    1 |       |    1 | 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID | TMP_ACC_DICT    |    1 |   154 |    1 | 00:00:01 |
| * 7 |       INDEX UNIQUE SCAN          | TMP_ACC_DICT_PK |    1 |       |    0 | 00:00:01 |
| * 8 |     TABLE ACCESS BY INDEX ROWID  | TMP_ACC_XM      |    1 |    22 |    1 | 00:00:01 |
| * 9 |      INDEX RANGE SCAN            | TMP_ACC_XM_FK   |    2 |       |    0 | 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("B"."FILTER_DATE">TO_DATE(:1,'dd.mm.yyyy'))
* 7 - access("A"."ACC_ID"="B"."ACC_ID")
* 8 - filter("SD"(+)>TRUNC(SYSDATE@!,'fmdd'))
* 9 - access("A"."ACC_ID"="ACC_ID"(+))