各位大佬下面语句有办法进行优化 让它的查询变得更快嘛
Select a.*
From (select /*+first_rows*/
wi.iid as 顺序号,
w.wname as 案件类型,
i.name as 名称,
x.联系人 as 联系人,
wi.accepted_time as 接收时间,
wi.submit_time as 提交时间,
(Select user_name From st_user Where userid = wi.senduserid) as 前一用户,
wi.wiid,
wi.step as 所在岗位,
x.业务类型id as BusinessId
from ST_WORK_ITEM_HIST wi,
xt_instance x,
st_instance i,
st_workflow w
where wi.iid = x.iid
and x.iid = i.iid
and i.wid = w.wid
and wi.userid = '52AD6F18BD6C45809A83D3D1EA4CC1F21111'
and x.业务类型id in
(select id from sys_business_type b where b.type = 3)
order by wi.accepted_time desc) a
Where rownum <= 10
select /*+first_rows*/
wi.iid as 顺序号,
w.wname as 案件类型,
i.name as 名称,
x.联系人 as 联系人,
wi.accepted_time as 接收时间,
wi.submit_time as 提交时间,
u.user_name as 前一用户,
wi.wiid,
wi.step as 所在岗位,
x.业务类型id as BusinessId
from ST_WORK_ITEM_HIST wi,
xt_instance x,
st_instance i,
st_workflow w
left join st_user u on u.userid=wi.senduserid
where wi.iid = x.iid
and x.iid = i.iid
and i.wid = w.wid
and wi.userid = '52AD6F18BD6C45809A83D3D1EA4CC1F21111'
and exists (select 1 from sys_business_type b where b.type = 3 and b.id=x.业务类型id)
order by wi.accepted_time desc
推荐建立索引:
ST_WORK_ITEM_HIST.iid
xt_instance.iid
st_instance.iid
st_instance.wid
st_workflow.wid
不知道数据量是很难优化的,首先你看看表的数据量,把数据量最大的表写到最后面,然后条件就是把能过滤最多的数据条件放在最后面,这样就能加快不少速度
请发出来执行计划，根据计划进行分析后才能得出合理的性能优化方案
针对CBO模式，感觉从sql代码来看，基本没有优化的空间，不过可以看看这段：
(select id from sys_business_type b where b.type = 3)
是否需要给这个id去一下重