我的一条SQL，能够执行出数据。一下是sql和执行计划
with t1 as (select
ms.row_id staff_id
from
md_staff ms ) select
a.row_id,
a.staff_id,
d.code,
d.short_code,
d.name,
d.en_full_name,
d.sex,
g.name company_name,
c.name dept_name,
d.education,
d.political_status,
d.last_upd_date,
d.status
from
t1 e,
md_post a
left join
view_org_unit_path b
on a.dept_id = b.row_id
left join
cd_code c
on b.row_id = c.row_id
left join
view_org_unit_path f
on f.row_id = c.company_id
left join
cd_code g
on f.row_id = g.row_id
left join
view_md_staff d
on a.staff_id = d.row_id
where
a.is_main = '1' and
a.staff_id = e.staff_id
因为是用的集成平台，由平台自动分页，所以平台会对sql进行一定的修改，修改后sql就慢的无法执行处结果了，如下（sql和执行计划）
select
*
from
( with t1 as (select
ms.row_id staff_id
from
md_staff ms ) select
a.row_id,
a.staff_id,
d.code,
d.short_code,
d.name,
d.en_full_name,
d.sex,
g.name company_name,
c.name dept_name,
d.education,
d.political_status,
d.last_upd_date,
d.status
from
t1 e,
md_post a
left join
view_org_unit_path b
on a.dept_id = b.row_id
left join
cd_code c
on b.row_id = c.row_id
left join
view_org_unit_path f
on f.row_id = c.company_id
left join
cd_code g
on f.row_id = g.row_id
left join
view_md_staff d
on a.staff_id = d.row_id
where
a.is_main = '1' and
a.staff_id = e.staff_id
order by
g.order_id,
g.code,
c.order_id,
c.code,
d.order_id,
d.code )
where
rownum <= 10
执行计划明明显示的很少，但是无法执行处结果。只是在sql外增加了一个select * from () num < 10
------解决思路----------------------
重新采集下这几个表的统计信息试试
------解决思路----------------------
select a.row_id,
a.staff_id,
d.code,
d.short_code,
d.name,
d.en_full_name,
d.sex,
g.name company_name,
c.name dept_name,
d.education,
d.political_status,
d.last_upd_date,
d.status
from md_post a
left join view_org_unit_path b
on a.dept_id = b.row_id
left join cd_code c
on b.row_id = c.row_id
left join view_org_unit_path f
on f.row_id = c.company_id
left join cd_code g
on f.row_id = g.row_id
left join view_md_staff d
on a.staff_id = d.row_id
where a.is_main = '1'
and exists
(select 1 from md_staff e where a.staff_id = e.staff_id)