create table tab1 (t1_id varchar2(12) constraint pk_tab1 primary key)

create table tab2 (t2_id varchar2(12) constraint pk_tab2 primary key)

create table tab3 (t3_id char(12))

create index idx_tab3_t3_id on tab3(t3_id)

insert into tab1(t1_id) select to_char(level,'fmRM') from dual connect by level < 1000

insert into tab2(t2_id) select to_char(level+100,'fmRM') from dual connect by level < 1000

insert into tab3(t3_id) select to_char(nullif(mod(level,777),0),'fmRM') from dual connect by level <20000

commit

create or replace view v1 as  
select t3_id id,count(t3_id) cnt from tab3 group by t3_id having count(t3_id) > 0

create or replace view v2 as 
select tab2.t2_id id, nvl(v1.cnt,0) cnt from tab2, v1 
where tab2.t2_id = v1.id (+)

create or replace view v3 as 
select tab1.t1_id id, v2.cnt from tab1, v2 where tab1.t1_id = v2.id and v2.cnt > 0

select * from v3

select * from v3 where id = 'CLII'

select * from v3 where id like 'CLI%'

drop table tab1 purge

drop table tab2 purge

drop table tab3 purge

drop view v1

drop view v2

drop view v3