Oracle in子句 效率低下

Question

啥也不说了，贴上sql：
create or replace view my_project_view as
select *
from 表A t where status='XXX' and pro_status='x' and ( flag <>'x' or flag is null ) and
( LEAD_ID = fruit_view_param.get_paramthree() or t.auto_id in
(select PROJECT_ID from 表B where LEAD_ID=fruit_view_param.get_paramthree()
or AUTO_ID in ( select PROJECTIMPL_ID from 表C where LEAD_ID=fruit_view_param.get_paramthree())
) );
问题：对于where xxx in 子句效率极其低下问题，求大神指教 。 我这个语句该如何去写。

Answer 1

create or replace view my_project_view as
select *
from 表A t where status='XXX' and pro_status='x' and ( flag <>'x' or flag is null ) and
( LEAD_ID = fruit_view_param.get_paramthree() or exists
(select PROJECT_ID from 表B where LEAD_ID=fruit_view_param.get_paramthree() and t.auto_id = PROJECT_ID
or exists ( select PROJECTIMPL_ID from 表C where LEAD_ID=fruit_view_param.get_paramthree() and AUTO_ID = PROJECTIMPL_ID
) );