1、首先会发现你的执行计划变动很大,导致数据出现此问题也比较常见 2、如果以前第1条的sql速度可以的话,第2条可以这么写 SELECT /*+MERGE(V) */ * from (原来SQL语句) V where rownum<=10 注:具体原因和hint用法 参照相关oracle文档
看图片,这是在pl/sql dev中执行的。第一条,结果集的估算有4万多,但是你在pl/sql dev中应该只返回了前几条,造成了执行很快的假象 第二条,因为有个order by,因此虽然只返回10条,却要先将4万多的结果集先全部返回,并在内存中排序可以在第一条的外层加一个select count(*) from (),看看执行时间怎么样
试试在cd_code表上建组合索引(order_id,code,row_id,name)
我按照你的说法试了(count),速度也很快
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)
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)
2、如果以前第1条的sql速度可以的话,第2条可以这么写
SELECT /*+MERGE(V) */ *
from (原来SQL语句) V
where rownum<=10
注:具体原因和hint用法 参照相关oracle文档
第二条,因为有个order by,因此虽然只返回10条,却要先将4万多的结果集先全部返回,并在内存中排序可以在第一条的外层加一个select count(*) from (),看看执行时间怎么样
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)
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)