sql 数据优化 pl/sql oracle

Question

各位大佬下面语句有办法进行优化 让它的查询变得更快嘛

 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

Answer 1

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

Answer 2

不知道数据量是很难优化的,首先你看看表的数据量,把数据量最大的表写到最后面,然后条件就是把能过滤最多的数据条件放在最后面,这样就能加快不少速度

Answer 3

请发出来执行计划，根据计划进行分析后才能得出合理的性能优化方案

Answer 4

针对CBO模式，感觉从sql代码来看，基本没有优化的空间，不过可以看看这段：

(select id from sys_business_type b where b.type = 3)

是否需要给这个id去一下重