请帮忙看看使用关联查询或者是with.. as能否优化如下sql语句?

[wangyangcheng]

select bc.corpid, bc.memberid, bc.feenumber, bc.shortnumber, bc.groupflag,
ci.validflag,
cf.feetype
from corpmember bc, corpinfo ci, corpfeetype cf
where bc.shortNumber=:1
and bc.corpid=ci.corpid
and ci.corpid=cf.corpid
and ci.corpid in (select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )

各位有不有其它的优化办法?

由于括号里面的select子句可能会返回多行结果,我觉得使用with..as没什么意义了.

如果是使用corelated subquiries是否有性能提升呢?

[shiyiwan]

SELECT BC.CORPID, BC.MEMBERID, BC.FEENUMBER, BC.SHORTNUMBER, BC.GROUPFLAG, CI.VALIDFLAG, CF.FEETYPE
  FROM CORPMEMBER BC, CORPINFO CI, CORPFEETYPE CF
 WHERE BC.SHORTNUMBER = :1
   AND BC.CORPID = CI.CORPID
   AND CI.CORPID = CF.CORPID
   AND CI.PARENTCORPID IS NOT NULL
   AND CI.PARENTCORPID = (SELECT PARENTCORPID
                            FROM CORPINFO
                           WHERE PARENTCORPID <> 0
                             AND CORPID = :2
                             AND ROWNUM <= 1)

[music_mouse]

尝试一下用exists 替换in

[wangyangcheng]

引用 楼主 wangyangcheng 的回复:

select bc.corpid, bc.memberid, bc.feenumber, bc.shortnumber, bc.groupflag, ci.validflag, cf.feetype from corpmember bc, corpinfo ci, corpfeetype cf where bc.shortNumber=:1 ……

(select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 ) 上面这个子句可能是返回多行记录结果的.

[wangyangcheng]

引用 2 楼 music_mouse 的回复:

尝试一下用exists 替换in

这个效率差不多的吧?

[wangyangcheng]

上面这个我仔细分析了一下，感觉还真没有优化空间了。 需要请高手再帮忙分析一下。

[dut703]

试下 select bc.corpid, bc.memberid, bc.feenumber, bc.shortnumber, bc.groupflag, ci.validflag, cf.feetype from corpmember bc, corpinfo ci, corpfeetype cf where bc.shortNumber=:1 and bc.corpid=ci.corpid and ci.corpid=cf.corpid and ci.corpid is not null and ci.corpid in (select /*+ no_merge */ c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 )

[music_mouse]

如果前面的条件都要强制关联的话，那 ci.corpid in (select c1.corpid from corpinfo c1, corpinfo c2 where c1.PARENTCORPID = c2.PARENTCORPID and c2.PARENTCORPID<>0 and c2.corpid=:2 ) 优化的空间只能在这里了