oracle的涉及大数据量之查询语句优化

Question

wtpart 表 数据量 千万级
stringvalue 表 数据量 十亿级
其他表均在百万级以下
限制：stringvalue字段无法建索引
问题：有时查询时间超30min
希望：提高查询效率

select ida2a2 as partId from (select rownum as rn,t.ida2a2
 from wtpart t inner join (
 select sv.ida3a4 from stringvalue sv
 join StringDefinition sd on sd.ida2a2=sv.ida3a6 
 where sd.name='figureno' and  sv.value like '0B' 
 )temp on temp.ida3a4=t.ida2a2 
 where t.versionida2versioninfo='A' and t.iterationida2iterationinfo='1'
  and t.ida3view=(select ida2a2 from wtview where name='EBOM')
    and  rownum<=50 )where rn>0

本地的执行计划

Answer 1

WITH W AS
 (select ida2a2 from wtview where name = 'EBOM'),
temp AS
 (select sv.ida3a4
    from stringvalue sv, StringDefinition sd
   where sd.name = 'figureno'
     AND sd.ida2a2 = sv.ida3a6
     and sv.value like '0B')
--看是否需要建这个索引：StringDefinition(ida2a2,name) 
select ida2a2 as partId
  from (select rownum as rn, t.ida2a2
          from wtpart t, W
         where t.versionida2versioninfo = 'A'
           and t.iterationida2iterationinfo = '1'
           and t.ida3view = W.ida2a2
                    --关键点：把大表关联调整为存在性的判断
           and exists (select 1 from temp where temp.ida3a4 = t.ida2a2)
           and rownum <= 50)
 where rn > 0